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))
I have an example tab (red) and the table use is "3 Month Forecast". I'd appreciate any help you can provide!