Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic total sum by dimension

Hi everyone,

 

Hope you guys fine and safe.

 

Here is my thing, I want a dynamic total sum based on two dimensions (Continent and Country). 

What I want is when I switch dimensions it gives me the total sum of the selected dimension. 

For now I know how to do it but not dynamically : 

Dynamic Total sales = CALCULATE(SUM('Requête1'[Sales]),ALL('Requête1'[Country]))
I have to put rather country or  continent but of course when I switch dimensions it does not make sense anymore.
 
Is it possible to do it dynamically ?
 
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks amitchandak 

     

    Here is the thing, when I am at a continent level granularity I want the total sum of all continent like this below: 

     

    and when I am at a country level I want the sum to perform like this : 

     

    Hope this make it clear enough,

     

    Thanks a lot for your time

     

     

  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Try this:

    Dynamic Total sales =
SWITCH (
    TRUE (),
    ISFILTERED ( 'Requête1'[Country] ),
        CALCULATE (
            SUM ( 'Requête1'[Sales] ),
            ALLEXCEPT ( 'Requête1', 'Requête1'[Continent] )
        ),
    ISFILTERED ( 'Requête1'[Continent] ), CALCULATE ( SUM ( 'Requête1'[Sales] ), ALL ( 'Requête1'[Country] ) )
)

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks amitchandak 

     

    Here is the thing, when I am at a continent level granularity I want the total sum of all continent like this below: 

     

    and when I am at a country level I want the sum to perform like this : 

     

    Hope this make it clear enough,

     

    Thanks a lot for your time

     

     

    • Icey's avatar
      Icey
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous ,

       

      Try this:

      Dynamic Total sales =
SWITCH (
    TRUE (),
    ISFILTERED ( 'Requête1'[Country] ),
        CALCULATE (
            SUM ( 'Requête1'[Sales] ),
            ALLEXCEPT ( 'Requête1', 'Requête1'[Continent] )
        ),
    ISFILTERED ( 'Requête1'[Continent] ), CALCULATE ( SUM ( 'Requête1'[Sales] ), ALL ( 'Requête1'[Country] ) )
)

       

       

      Best regards

      Icey

       

      If this post helps, then consider Accepting it as the solution to help other members find it faster.