Forum Discussion

HobbyS's avatar
HobbyS
Regular Visitor
3 years ago

Group Total on Specific Row

I have to following Table:

 

and the Following Dax-Code for myMeasure:

=CALCULATE(SUM(Tabelle1[Amount]); ALL(Tabelle1[Currency]))

 

I would like to have the total of all Currencies (700) to show at the Row with Currency = CHF. Like that:

 

Could you please help me with my dax formula? I tried to add an additional filter with Currency = CHF, but then i get 0 as result.

 

Thank you in advance for your help.

 

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi HobbyS 

    (700) to show at the Row with Currency = CHF, what to show at other rows like EUR and USD?

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      or you looking for a measure like this?

       

      Measure = 
VAR _value = MAX(Tabelle1[Currency])
RETURN
IF(
    _value = "CHF",
    CALCULATE(SUM(Tabelle1[Amount]), ALL(Tabelle1[Currency])),
    SUM(Tabelle1[Amount])
)

       

       

      • HobbyS's avatar
        HobbyS
        Regular Visitor
        3 years ago

        Thank you for your reply. I adjusted the measure like this:

         

        =VAR _value = MAX(Tabelle1[Currency])
RETURN
IF(
    _value = "CHF";
    CALCULATE(SUM(Tabelle1[Amount]); ALL(Tabelle1[Currency]));
    0
)

         

        Because if currency <> CHF I want to have 0. The solution is almost perfect, but de Group Total does not show. (should also be 700):

        Do you also have a solution for this?

  • HobbyS's avatar
    HobbyS
    Regular Visitor
    3 years ago

    I have a solution:

    =IF( HASONEFILTER ( Tabelle1[Currency] ); VAR _value = MAX(Tabelle1[Currency])
RETURN
IF(
    _value = "CHF";
    -CALCULATE(SUM(Tabelle1[Amount]); ALL(Tabelle1[Currency]));
    0
);-SUM(Tabelle1[Amount]))

    So only if I have a Filter on Currency, the calculate-Formula is used. Otherwiese we are using total Amount (on Groups). Thank you FreemanZ  and Anonymous .