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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
swhitney1008
New Member

How to dynamically use different measures at the Grand Total vs. Filtered

Hello,

 

I have two different measures for calculating Turnover because the logic changes if the data is filtered: "Turnover % (Division)" and "Turnover % (Total)"

 

I'd like to have a third measure that dynamically switches between the two measures depending on if it's evaluating at the Grand Total. The below screenshot is showing a Matrix but I'd also be using this where it's controlled via a slicer (where no selection is made in the slicer = Grand Total).

swhitney1008_0-1673891278747.png

 

I've previously been able to switch between measures dynamically using IF(ISFILTERED(...) but that isn't helping here, likely because I need the Grand Total to work in the absence of any filter selection.

My current (not working) measure is below:

Turnover (Dynamic) % =
    //Department or Division
    IF(
        ISFILTERED('DeptStructure'[Department Code]) ||
        ISFILTERED('DeptStructure'[Division Code]))
            ,[Turnover % (Division)] ,

    //Hierarchy
    IF(
        ISFILTERED(DeptStructure[Division Code])&ISFILTERED(DeptStructure[Department Code])
            , [Turnover % (Division)] ,

    //Else
    [Turnover % (Total)]

    ))

 

Any help is greatly appreciated!

 

Thank you!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @swhitney1008 

please try the following 

 manually create a single column table that contains the names of the three measures 

create a measure as follows 

Turnover (Dynamic) % =
SWITCH (
    SELECTEDVALUE ( Measures[MeasureName] ),
    "Turnover % (Division)", [Turnover % (Division)],
    "Turnover % (Total)", [Turnover % (Total)],
    IF (
        ISFILTERED ( 'DeptStructure'[Department Code] ),
        [Turnover % (Division)],
        [Turnover % (Total)]
    )
)

Place Measures[MeasureName] in the columns of the matrix and place the above measure at the values.

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @swhitney1008 

please try the following 

 manually create a single column table that contains the names of the three measures 

create a measure as follows 

Turnover (Dynamic) % =
SWITCH (
    SELECTEDVALUE ( Measures[MeasureName] ),
    "Turnover % (Division)", [Turnover % (Division)],
    "Turnover % (Total)", [Turnover % (Total)],
    IF (
        ISFILTERED ( 'DeptStructure'[Department Code] ),
        [Turnover % (Division)],
        [Turnover % (Total)]
    )
)

Place Measures[MeasureName] in the columns of the matrix and place the above measure at the values.

I think that did the trick! 

I'm happy to know I was somewhat close with how I was approaching it.

 

Thanks so much!

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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