Total of grouped calculation

[laryssamorato]

Hi Guys, I really need your help here.

I'm only using power BI since december.

I'm stuck in something since weeks and I have already tried lots of pages to see if someone have already had the same question, but no success on that.

So lets go to the issue:

First I have a basic table (Table 1) with this info:  Adj Sold, Sold, Material, Plant. The Adj Sold is a column where I adjust the Solds/Clients that comes wrong from the system.

Then I have a big table (Table 2) with 3 levels of information (sold, material, plant) and in this table I have the forecast and sales information by month.

Both tables are related with a key (sold, material, plant).

What I want to do is to subtract (ABS) "Forecast" - "Sales" (Table 2) for each combination of Adj Sold, Material, Plant (Table 1) that are on my Table 2 and return the total of this calculation, by month.

Example:

Table 1

Adj Sold	Sold	Material	Plant
Embol	Embol- BH	20524512	503
Embol	Polar LTDA	20524512	503
Embol	Embolac	20524512	503
Subway	RFG COMERCIO	20697359	501
Subway	HELA SPICE LATAM	20697359	501
Subway	Martin Brower	20697359	501

Table 2

Sold	Material	Plant	Period	Forecast	Sales
Embol- BH	20524512	503	jan/20	5.350	4.800
Polar LTDA	20524512	503	jan/20	4.200	5.000
Embolac	20524512	503	jan/20	1.350	1.300
RFG COMERCIO	20697359	501	jan/20	10.260	9.500
HELA SPICE LATAM	20697359	501	jan/20	7.000	7.000
Martin Brower	20697359	501	jan/20	5.000	42.500
Embol- BH	20524512	503	fev/20	2.012	0
Polar LTDA	20524512	503	fev/20	500	3.200
Embolac	20524512	503	fev/20	5.148	6.980
RFG COMERCIO	20697359	501	fev/20	12.293	9.000
HELA SPICE LATAM	20697359	501	fev/20	28.035	21.000
Martin Brower	20697359	501	fev/20	10.000	500

Measure result (Total of ABS error) :

Adj Sold	Material	Plant	Months	Forecast	Sales	ABS Error
Embol	20524512	503	jan/20	10.900	11.100	200
Embol	20524512	503	fev/20	7.660	10.180	2.520
Subway	20697359	501	jan/20	22.260	59.000	36.740
Subway	20697359	501	fev/20	50.328	30.500	19.828
Grand Total				91.148	110.780	59.288

Just for you to understand why I need this: if I just do the subtraction on table 2, instead of 59.288 I would get 66.031.

It might not seem much, but this is just a example. On my real database, I get much more different totals.

All measures I tried I only get a simple calculation of "total forecast - total sales" which in this example would be 19.632. But that's not the number I need.

So, how can I get this result using a measure ?

Thank you guys! I really need this help!

[v-lili6-msft]

hi  @laryssamorato 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and then you could add a new measure like PaulDBrown's reply.

Regards,

Lin

[PaulDBrown · Accepted solution]

@laryssamorato 

I have replicated your data and this is what I get for non ABS values:

For ABS values, the outcome depends on how you "make" the calculations.

1) If you simply summarize the ABS values on the table visual level, you get the 59.288.

Make ABS = IF([Sales - Forecast]<0; [Forecast - Sales]; [Sales - Forecast])

Indiv Row ABS Sales - Forecast calc = SUMX(SUMMARIZE(Table2; 'Adj Sold Table'[Adj Sold]; Material[Material];Plant[Plant];'Period Table'[Period]; "calc" ; [Make ABS]); [calc])

2) You need to force the DAX to calculate on a row by row level (ABS Values) and then sum them up:

Indiv Row ABS Sales - Forecast = SUMX(Table2; [Make ABS])

Here is the comparison:

[laryssamorato]

@PaulDBrown you just saved my life! IT WORKED! I'm crying 😭

I spent so much time on this, you have no idea!

TKS A LOT!!!!

[PaulDBrown]

@laryssamorato 

Very glad I could be of help.

(I Think you worked out that there is no need for "table 3" . I only replicated your "table 3" to illustrate that the results differ depending on the DAX. The correct measure in your case is the second:

Indiv Row ABS Sales - Forecast = SUMX(Table2; [Make ABS])

[laryssamorato]

@PaulDBrown 

Yes! in the first moment I was confused, but then I understood the sumx in the beggining. No need of table 3. 😁

Thank you again!

[laryssamorato]

Hi @PaulDBrown !

The problem is that I only have table 1 and table 2. The 3rd one I just included on my post to examplify what would be the calculation to get the ABS calculation for each level (adj sold, material, plant,period).

So do I have to create the 3rd table using summarize?

Is that even possible using the both Tables 1 and 2?