Forum Discussion
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.
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 🙂
3 Replies
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 🙂
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?
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 🙂