cancel
Showing results for
Did you mean:
Frequent Visitor

Moving Average Column Per Category

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.

1 ACCEPTED SOLUTION
Super User IV

Add the following code as a new table:

``````Moving Average =

CALCULATE(
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 🙂

Proud to be a Super User!

3 REPLIES 3
Super User IV

Add the following code as a new table:

``````Moving Average =

CALCULATE(
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 🙂

Proud to be a Super User!

Frequent Visitor

Thank you so much for the quick response.

This worked, I have a query regarding this formula.

``````Moving Average =

CALCULATE(
,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?

Super User IV

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 🙂

Proud to be a Super User!

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!