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

[swhitney1008]

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).

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!

[tamerj1 · Accepted solution]

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.

[swhitney1008]

I think that did the trick! 

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

Thanks so much!