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.

     

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
Community 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.