Forum Discussion
Problem with total sums and data model.
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
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])
2 Replies
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])Hi Anonymous
Please try= SUMX ( SUMMARIZE ( Master, Master[Model], Master[Submodel] ), CALCULATE ( ABS ( SUM ( Orders[units] ) - SUM ( Forecast[Demand] ) ) ) )
