Forum Discussion
Sum elements in a table discriminating one dimension
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
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] ) ) ) )
2 Replies
- Jihwan_Kim
Super User
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] ) ) ) )