cancel
Showing results 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

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:

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

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.

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:

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

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.

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://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/