Problem with total sums and data model.

[Anonymous]

Hello,

I have the following example data:

Model	Submodel	Sold units	demand forecast	ABS(sold units-demand)
x	x1	3	2	1
x	x2	4	7	3
x	x3	15	4	11
x	x4	6	4	2

Data model is simple: Two different facts table, one for Orders and one for Sold units. Related to them, a Dimension table called "Master" with SKUs as Keys.

I would like now to sum the different abs values (0+1+1+2) in model granularity, but instead, when applying to a Matrix, it calculates the abs as the following:

(3+4+15+6) - (2+7+4+4) = 28-17 =11

While I would like it to sum it like: (1+3+11+2) = 17

So the table would be:

Model	Sold units	Demand Forecast	sum of Abs
x	28	17	17

I have been trying everything and searching around. What is the proper way of doing this, and understanding it?

Could someone provide me with a correct formula?

Thanks in advance

[DimaMD · Accepted solution]

hi @Anonymous You need two measures

abs = 
 VAR _abs = SUM('example data'[Sold units]) - SUM('example data'[demand forecast])
 return
 ABS(_abs)


measure_total = SUMX( VALUES('example data'[Submodel]),[abs]) 

[tamerj1]

Hi @Anonymous 
Please try

=
SUMX (
    SUMMARIZE ( Master, Master[Model], Master[Submodel] ),
    CALCULATE ( ABS ( SUM ( Orders[units] ) - SUM ( Forecast[Demand] ) ) )
)