Moving Average Column Per Category

[Anonymous]

Need DAX formula to get the MOVING AVG PER CATEGORY column (highlighted in green)

The result is average of top rows of a category i.e.,

Category A:

   Row 2 is average of EMP HEADCOUNT 314, 317

   Row 3 is average of EMP HEADCOUNT 314, 317 and 319 and so on.

  This should go on groupby category

Category B:

  Row 7 is average of 16, 15

  Row 8 is average of 16, 15 ,13

Category C:

  Row 12 is average of 27, 29

I hope i explained this correctly. Please reply for any further clarifications.

Thanks.

[Fowmy · Accepted solution]

@Anonymous 

Add the following code as a new table:

Moving Average = 

CALCULATE(
    AVERAGE(Table1[EMP HEADCOUNT]),
    Table1[MONTH] <= EARLIER(Table1[MONTH]),
    ALLEXCEPT(Table1,Table1[CATEGORY])
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thank you so much for the quick response.

This worked, I have a query regarding this formula.

Moving Average = 

CALCULATE(
    AVERAGE(Table1[EMP HEADCOUNT])
    ,Table1[MONTH] <= EARLIER(Table1[MONTH])
)

If I remove the allexcept function in the above query, this should give me moving average irrespective of category. However, it is resulting in same output column as EMP HEADCOUNT. What is the logic behind this?

[Fowmy]

@Anonymous 

ALLEXCEPT removes the filter From all the columns except the Category which allows the average to see the rows of that category, then, we filter months less than current month using earlier. 

when you exclude ALEXCEPT, you only see the current row. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn