The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
hi @orderstatus_shi 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 @orderstatus_shi 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 @orderstatus_shi
Please try
=
SUMX (
SUMMARIZE ( Master, Master[Model], Master[Submodel] ),
CALCULATE ( ABS ( SUM ( Orders[units] ) - SUM ( Forecast[Demand] ) ) )
)
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
15 | |
12 | |
12 | |
7 |