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