sum absolute value totals based on groups

[Anonymous]

Hello everyone,

How do you sum the total of the absolute difference of sub groups of records?

I'm grouping SKUs by their Group_ID and I want to compare the absolute change between two forecast snapshots at the group.

How I’m calculating the variation

• Unit 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.

Where I’m stuck:

The Unit Change Abs measure is the problem.  It calculates fine for each row in the table view, but the grand total isn't summarizing as I'd like.  The result displays as 41,183,815 (which is ABS(367,859,244 – 326,675,429)).  However I want it to calculate as 41,306,445 (SUM of 500,431 + 61,315 + 40,744,699)

Thanks in advance for any recommendations you have for addressing this problem.

 

[Anonymous]

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]

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]

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]

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]

Here are the key relationships.  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.

I use the date table to reference the forecast month for forecast 1 and forecast 2, not their snapshot date. 

[vitalities]

Hi guys, I am facing the same issue. Have you achieved the expected result?