Power Pivot SUMX with GROUPBY filter - Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2424154%22%20slang%3D%22en-US%22%3EPower%20Pivot%20SUMX%20with%20GROUPBY%20filter%20-%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2424154%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20spent%20all%20day%20searching%20google%2FYouTube%2C%20twisting%20my%20DAX%20formula%20in%20Power%20Pivot%20around%3B%20and%20have%20finally%20throw%20my%20hands%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20end%20result%20I'm%20working%20towards%20is%20a%20%223%20Month%20Forecast%20Accuracy%22%20metric%3B%20typically%20calculated%20by%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%20-%20Abs(%20(Total%203%20Month's%20Invoices%20-%20Total%203%20Month's%20Forecast)%20%2F%20Total%203%20Month's%20Forecast)%3C%2FP%3E%3CP%3Eie.%201%20-%20error%20gives%20me%20the%20accuracy.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMonthly%20forecast%20is%20imported%20with%20every%20row%20equaling%20a%20forecast%20month%20of%20the%20Customer%20%2F%20Item%20combination%3B%20so%20the%20route%20I've%20been%20taking%20has%20been%20to%20groupby%20%22Key%20Customer%20Name%22%20and%20%22Item%20Group%22%3B%20and%20sumx%20because%20I%20want%20to%20total%20invoices%20minus%20total%20forecast%20before%20dividing%20by%20the%20total%203%20month%20forecast.%26nbsp%3B%20I'm%20getting%20the%20appropriate%20results%20at%20the%20line%20%26amp%3B%20subtotal%20level%2C%20but%20the%20grand%20total%20(3%20Month%20Forecast%20Accuracy%20Roll-up)%20is%20omitting%20the%20grouping.%26nbsp%3B%20ie.%20GT%20%223%20Month%20Absolute%20Error%22%20should%20be%2019%2C483%3B%20with%20an%20accuracy%20of%2023%25%20(1%20-%20(19%2C493%2F25%2C150))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287031i8FD96E194EABEA06%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20an%20example%20tab%20(red)%20and%20the%20table%20use%20is%20%223%20Month%20Forecast%22.%26nbsp%3B%20I'd%20appreciate%20any%20help%20you%20can%20provide!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2424154%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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