sum absolute value totals based on groups

Question

Hello everyone,How do you sum the total of the absolute difference of sub groups of records?&nbsp;I'm grouping SKUs by their Group_ID and I want to compare the absolute change between two forecast snapshots at the&nbsp;group.&nbsp;How I’m calculating the variationUnit Change = SUM('Forecast Table 1'[forecast]) - SUM('Forecast Table 2'[forecast])Unit Change Abs = ABS(SUM('Forecast Table 1'[forecast]) - SUM('Forecast Table 2'[forecast]))Unit Change % = [Unit Change]/SUM('Forecast Table 2'[forecast])Below is an example of the results.&nbsp;Where I’m stuck:The Unit Change Abs measure is the problem.&nbsp; It calculates fine for each row in the table view, but the grand total isn't summarizing as I'd like.&nbsp; The result displays as 41,183,815 (which is ABS(367,859,244 – 326,675,429)).&nbsp;&nbsp;However I want it to calculate as 41,306,445 (SUM of 500,431 + 61,315 + 40,744,699)&nbsp;&nbsp;Thanks in advance for any recommendations you have for addressing this problem.&nbsp;

anonymous · Answer

Instead of measure create a new column, something like

Column_Unit Change = ABS(('Forecast Table 1'[forecast]) - Related('Forecast Table 2'[forecast]))

*assuming both tables are related in mange relationships.

Use this colmn alongwith measures in your matrix and set its aggregation to 'SUM'

anonymous · Answer

Instead of measure create a new column, something like

Column_Unit Change = ABS(('Forecast Table 1'[forecast]) - Related('Forecast Table 2'[forecast]))

*assuming both tables are related in mange relationships.

Use this colmn alongwith measures in your matrix and set its aggregation to 'SUM'

anonymous · Answer

Thanks yashmanu0123, although I don't think this will work as they aren't related. The reason is because I don't want the snapshot date the users selects for the slicer from forecast 1 to filter the snapshot date the user selects from the forecast 2 table.

Forecast table 2 is a copy of table 1 so they both have the same data. (Columns for both are SKU, Group_ID, Snapshot Date ,Forecast Month, Forecast)

I created a calendar table and SKU table that both forecast tables have relationships with so that I can use both tables in the same table view.

Any other recommendations?

anonymous · Answer

I tried a similar scenario 2 forecast tables and 1 group/sku table for relationship.

I was able to create column using RELATED function. Maybe I am missing something.

Can you share a snapshot of your data model?

anonymous · Answer

&nbsp;Here are the key relationships.&nbsp; There are others I've cropped out for simplicity sake.I want to be able to filter by snapshot date, business, Group, and time horizon so I have lookup tables for each.&nbsp;I use the date table to reference the forecast month for forecast 1 and forecast 2, not their snapshot date.&nbsp;&nbsp;

Forum Discussion

sum absolute value totals based on groups

6 Replies