Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 ?
 
 

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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: Capture1.JPG

 

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

Capture2.JPG

 

Hope this make it clear enough,

 

Thanks a lot for your time

 

 

View solution in original post

Icey
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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: Capture1.JPG

 

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

Capture2.JPG

 

Hope this make it clear enough,

 

Thanks a lot for your time

 

 

Icey
Community Support
Community Support

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
Super User
Super User

@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/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.