Limiting results on filtered dashboard

[jeffminder]

This is for a score tracking app I've been working on...

Currently have a three-pane filtered dashboard.  Below is a brief description of what each contains.  The average at the bottom of the left pane was recently added, and is doing what it says.  The problem I've found is that I don't want an average of ALL data, as the most recent X items is a better indicator.  

Thinking about how do make this better...   would it be possible to add another filter on the left to indicate how many items to average?   Not sure if this is hard/easy, but thought it may be worth asking the crowd before jumping off a cliff.   Any suuggest?

- Left:  4 enum-list filters, text box containing the average of data in center pane.
- Center:  card view of data shown based on filter criteria (4 csv numbers:  "1,3,5,7")
- Right: Graph of 4 numbers from center pane

[baba_sawane]

Hi jeff,

Presuming your values are already in date order and you only want to update the average formula,

create a virtual column 'maxrow' with max(csvtable, [_rownumber])

for the average:  average(filter(csvtable[csvvalues], [_rownumber]>[maxrow]-X) ) 

I have no idea if it will work 😀 If it does I suggest you also format in green the last 8 values used to calculate the average, using the same formula as condition in format rules.

[baba_sawane]

update: use select instead of filter  :  average(select(csvtable[csvvalues],[_RowNumber] - X ) )

[jeffminder]

Will give this a shot this week...   You made it sound easy.  🙂

[baba_sawane]

In fact, it'll be more tricky, you can't average([csvvalues]) as there are multiples values. 

How are you calculating the actual average estimate ? 

[jeffminder]

What is displayed in that view is a chunk of CSV data. However, do have access to each number individually. This is the code that computes the portion in green on the bottom left for one number. Then concatenate the four values together to get it back in CSV form. Code below rounds one of the CSV numbers to the nearest 0.25.

The 'AND' section is also used in the filter section to control what the Middle & Right panes are displaying. Seems this is where I would need to inject your suggestion...

To be honest I don't fully understand how the 'AND' portion does what it does.  Just kept searching and looking at examples until I got it to work.

0.25*Round(4*(
        AVERAGE(
          SELECT(
            ELEV[OH],
            AND(
              if(isnotblank(EFilter[BLDG]), in([BLDG], EFilter[BLDG]), True),
              if(isnotblank(EFilter[Floor]), in([Floor], EFilter[Floor]), True),
              if(isnotblank(EFilter[City]), in([City], EFilter[City]), True),
              if(isnotblank(EFilter[Other]), in([Other], EFilter[Other]), True)
            )
         )
      )
   )
)

[jeffminder · Accepted solution]

Slightly different approach is now working!
Have access to [date] as a field in this row.  Being able to use a slider (daysAgo) to give data this far back works after adding the 5th If-statement.

AND(
       if(isnotblank(EFilter[BLDG]), in([BLDG], EFilter[BLDG]), True),
       if(isnotblank(EFilter[Floor]), in([Floor], EFilter[Floor]), True),
       if(isnotblank(EFilter[City]), in([City], EFilter[City]), True),
       if(isnotblank(EFilter[Other]), in([Other], EFilter[Other]), True),
       if(isnotblank(EFilter[daysAgo]),
         TotalHours(Today()-[Date])/24 < Max(EFilter[daysAgo]),True)
   )

Also interested in the possibility of using  _rowNumber to filter items, but that's a function of the parent of the slice, which has many rows I'm not interested in.   If I were to apply calculations such as _rownumber - X, how would you have any idea what would be returned?  Not against the (x-rows) approach, just don't fully understand how it would be evaluated.