topic Re: How to dynamically use different measures at the Grand Total vs. Filtered in DAX Commands and Tips

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

swhitney1008 — Mon, 16 Jan 2023 18:06:13 GMT

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!

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

tamerj1 — Mon, 16 Jan 2023 18:47:52 GMT

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.

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

swhitney1008 — Mon, 16 Jan 2023 20:00:24 GMT

I think that did the trick!

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

Thanks so much!