Dynamic total sum by dimension

[Anonymous]

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 ?

[Anonymous · Accepted solution]

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

[Anonymous · Accepted solution]

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 · Accepted solution]

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.

[amitchandak]

@Anonymous , Not very clear. If you want a Grand total by any of these. Unless there is null value all(Sales), all(Country), or all(Continent) will give same value

If you want to change axis. Refer to :

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/