Forum Discussion
Moving Average Column Per Category
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 🙂
Super UserAnonymous
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?
- 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 🙂