cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Example.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Thanks.

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@seefadeeb 

Add the following code as a new table:

Moving Average = 

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

Fowmy_0-1603542520500.png

 

________________________

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

 







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@seefadeeb 

Add the following code as a new table:

Moving Average = 

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

Fowmy_0-1603542520500.png

 

________________________

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

 







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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?

@seefadeeb 

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.