Forum Discussion
Bad behavior dax subtotal
i everyone,
I am trying to calculate the total for the field result of the following table:
| Year | Quarter | Line | Total Days | Average Days | Total Items | % Effectiveness | Result |
| 2016 | 3 | ACA | 128 | 128 | 1 | 0.05 | 6,4 |
| 2016 | 3 | HIL | 1016 | 508 | 6 | 0.3 | 152,4 |
| 2016 | 3 | INV | 1989 | 994,5 | 10 | 0.5 | 497,25 |
| 2016 | 3 | TEJ | 528 | 264 | 3 | 0.15 | 39,6 |
| Grand Total | 3661 | 473,625 | 20 | 1 | 473,625 |
The right value I expect is 695.65.
But DAX is not adding the fields (6.4 + 152.4 + 497.25 + 39.6), instead it is multiplying the total fields Average Days (473,625) and [% Effectiveness] (1) . I created the following Measures to perform this calculation:
Average Days = AVERAGE(Table[Total Days])
% Effectiveness = SUM(Table[Total Items]) / CALCULATE(SUM(Table[Total Items]),ALLSELECTED(Table))
Result = Table[Average Days] * Table[% Effectiveness]
I appreciated your help.
3 Replies
You need to use SUMX(table,[Result]).
The functions ending in X are iterator, which in lay-mans terms means they will loop through the rows in the current filter context calculating the result for each row before performing the final aggregation.
Thanks for your answer, but using SUMX is the same behavior, the real problem is that it applies the calculation even in the totals, it only adds the previous records only when they come from a defined table or column, not measures, because this applies the calculation for each row.
Item Average Days % Effectiveness Result with DAX 1 128 0,05 128 * 0,05 = 6,4 2 508 0,30 508 * 0,30 = 152,4 3 994,5 0,50 994,5 * 0,50 = 497,25 4 264 0,15 264 * 0,15 = 39,6 Grand Total 473,625 1 473,625 * 1 = 473,625 Result = Table[Average Days] * Table[% Effectiveness]
I need another way to calculate this Result and being able to get the right value: 695,65
Thanks!!
