Calculated grouped average based on context filters and apply if condition to count rows

[jolazar]

Hello!

I'm trying to calculate an average table grouped by store. Once I've calculated it I would like to assign to this average number a codition and count the numbers of stores that matches it.

This is my aggregate average table that I've calculated in powerbi

on the yellow number I would like to assign different categories based on condition and count the stores that fall in it. The problem is that I want to count stores based on this new aggregated measure and not on the original table (and PBI doesn't automatically do it). I've also tried to create a new table with average values already computed but then it doesn't change when I apply context filters. To sum up, I would like to calculate a rolling average according to context filter date applied and on this computed valued count the stores that falls within some ranges.

This is the DAx I've used 

GROUPBY(

    Table,

    Table[STORE],

    Table[DATE_LOCAL],

    "CLASS", AVERAGEX ( CURRENTGROUP(),Table[CLASS])

)

Any help??

[v-stephen-msft · Accepted solution]

Hi @jolazar ,

If you want to calculate the rolling values based on stores, you'd better have an index column or other columns you could calculate based on it.

Rolling Average = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLSELECTED('Table'),[Index]<=MAX('Table'[Index])))

If your store names are in a certain order, you can also refer to the store.

Rolling Average1 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLSELECTED('Table'),[Store]<=MAX('Table'[Store])))

If you are still in doubt, it is best to provide some sample data along with the expected results. Remember to protect privacy.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.