Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am looking for a way to sum elements in a table discriminating one dimension while including another dimension.
Here in this example, I want the sum of errors for the "Jean" and "Shirt" calculated separately, while the months of Jan and Feb are compensating each other :
Ventes | Forecast | error | ||
Pant | jan | 10 | 0 | 10 |
Pant | feb | 0 | 15 | 15 |
Shirt | jan | 20 | 0 | 20 |
Shirt | feb | 10 | 10 | 0 |
40 | 25 | 25 |
Here we can see the error is only 25 units, because there is only 5 units of error for "Pant" (Jan and Feb are compensating each other), while the error for "Shirt" is 20 units.
Is there any way in DAX to reach this result ?
Thank you
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Ventes measure: =
CALCULATE( SUM(Data[Value]), Data[Attribute] = "Ventes")
Forecast measure: =
CALCULATE( SUM(Data[Value]), Data[Attribute] = "Forecast")
Error measure: =
IF (
ISINSCOPE ( 'Month'[Month] ),
ABS ( [Ventes measure:] - [Forecast measure:] ),
SUMX (
DISTINCT ( Category[Category] ),
CALCULATE (
ABS ( [Ventes measure:] - [Forecast measure:] ),
ALL ( 'Month'[Month No], 'Month'[Month] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Ventes measure: =
CALCULATE( SUM(Data[Value]), Data[Attribute] = "Ventes")
Forecast measure: =
CALCULATE( SUM(Data[Value]), Data[Attribute] = "Forecast")
Error measure: =
IF (
ISINSCOPE ( 'Month'[Month] ),
ABS ( [Ventes measure:] - [Forecast measure:] ),
SUMX (
DISTINCT ( Category[Category] ),
CALCULATE (
ABS ( [Ventes measure:] - [Forecast measure:] ),
ALL ( 'Month'[Month No], 'Month'[Month] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Adapted the code to my needs and it worked great. Thanks
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |