Forum Discussion

ajimenez's avatar
ajimenez
Frequent Visitor
9 years ago
Solved

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 1...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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.

     

OpenDataLab's avatar
OpenDataLab
Icon for Helper II rankHelper II
9 years ago

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's avatar
ajimenez
Frequent Visitor
9 years ago

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.

 

ItemAverage Days% EffectivenessResult with DAX
1128                   0,05128 * 0,05     = 6,4
2508                   0,30508 * 0,30     = 152,4
3994,5                   0,50994,5 * 0,50 = 497,25
4264                   0,15264 * 0,15    = 39,6
Grand Total473,625                      1473,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's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.