Bad behavior dax subtotal

[ajimenez]

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.

[OpenDataLab]

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.

[ajimenez]

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!!

[Vvelarde · Accepted solution]

@ajimenez

Hi, Try with this DAX

Result =
SUMX (
    SUMMARIZE (
        Table1;
        Table1[Line];
        "RESULTS"; [AverageDays] * [% Effectiveness]
    );
    [RESULTS]
)

Review Your Column in Summarize accord to your visual & Structure.