Different formatting types for two switch outcomes


#1

I have been working through the module for multi measure dynamics and have come across a problem. I have two slicers working from switch statements and have combined them. One selects dollars or units and the other selects viewing the outcome as a percentage or an actual value.

It is the second switch I am having trouble with - I can only format to see the result as either numbers OR % - I do not see how to say ‘in situation A show me the number, in situation B convert it into a %’

The statement I am using is:

Fcst vs Budget. =
SWITCH( TRUE(),
[Metric Selected 2] = “Actuals”, [Fcst Vs Budget],
[Metric Selected 2] = “%”, [Fcst Vs Budget %],
[Fcst Vs Budget] )

Is there a way to ensure that when % is selected the format is also %?


Formating issue when Combining Dynamic Grouping
Values & Percentages within same field
#2

Unfortunately there’s no manual way to do this.

You can though wrap certain results in the FORMAT function within the SWITCH statement.

Give something like this a try and see if it works for you.

FORMAT( [Measures], “0.00%” )


Dynamic Formatting of Selected Measure
#3

Thank you - it is frustrating after hours of working to get the switches to work but if I can do it within the measure that should be ok - how do I add the recommendation you made within the context of my formula above - I cant get it to work?


#4

I think like this should work

Fcst vs Budget. =
SWITCH( TRUE(),
[Metric Selected 2] = “Actuals”, [Fcst Vs Budget],
[Metric Selected 2] = “%”, FORMAT ( [Fcst Vs Budget %], “0.00%”),
[Fcst Vs Budget] )


#5

That worked perfectly, the model can now flick between %s and actuals using a slicer, perfect! Thank you.