Power Pivot SUMX with GROUPBY filter - Formula help

Regular Visitor

Good Afternoon,

 

I've spent all day searching google/YouTube, twisting my DAX formula in Power Pivot around; and have finally throw my hands up.

 

The end result I'm working towards is a "3 Month Forecast Accuracy" metric; typically calculated by:

 

1 - Abs( (Total 3 Month's Invoices - Total 3 Month's Forecast) / Total 3 Month's Forecast)

ie. 1 - error gives me the accuracy.

 

Monthly forecast is imported with every row equaling a forecast month of the Customer / Item combination; so the route I've been taking has been to groupby "Key Customer Name" and "Item Group"; and sumx because I want to total invoices minus total forecast before dividing by the total 3 month forecast.  I'm getting the appropriate results at the line & subtotal level, but the grand total (3 Month Forecast Accuracy Roll-up) is omitting the grouping.  ie. GT "3 Month Absolute Error" should be 19,483; with an accuracy of 23% (1 - (19,493/25,150))

 

Capture.PNG


I have an example tab (red) and the table use is "3 Month Forecast".  I'd appreciate any help you can provide!

 

Thank you,

0 Replies