4 Replies Latest reply on Aug 26, 2016 2:02 PM by David Li

# Conditionally format measure values as number vs. percentage

Hello. I have a table of various measure names - measure values. A parameter allows one to select whether raw figures of % of a specific figure is shown. Each measure value contains the statements executing something like...

IF Parameter = "Raw data"

THEN (show raw data)

ELSEIF Parameter = "%"

THEN (show raw data / SUM (something))

END

However, depending on the parameter value selected, I end with either

a) meaningful figures e.g. 23,478 and meaningless percentages e.g. 1, 0

or

b) non-percentages displayed for figures e.g. 77000.00% and meaningful percentages e.g. 0.74%

Does a way exist for me to get meaningful figures and meaningful percentages?

Thanks.

• ###### 1. Re: Conditionally format measure values as number vs. percentage

Hi Li! One neat trick that will work here is to split that measure value into two calculations, each of which becomes an empty string if it isn't being used. Then, you just put both of them into your label mark.

IIF(Parameter = "Raw data", [Raw Data], "")

IIF(Parameter = "%", [Raw Data]/[WhateverSum], "")

Set the default number format of the first one as a long number and the second one as a percentage.

Now, drop both of these measures into the Label mark. They'll technically both show up, but since one is an empty string, it'll be hidden. This is also a useful trick for doing conditional formatting.

Note that for the second formula, you'll probably want to use a LOD calculation of some sort for the denominator.

• ###### 2. Re: Conditionally format measure values as number vs. percentage

That's pretty cool David I haven't seen that method before.  Here is a link that describes having multiple formats for a single measure.  I believe the solution in this post doesn't deal with negative numbers but it is possible to modify the solution so it does support negatives.

• ###### 3. Re: Conditionally format measure values as number vs. percentage

Many thanks, David. It does the trick! The alignment goes a bit awry though and I am not sure if it has to do with show/no-show of the measures... just happy it works. Ingenious.

THANK YOU

• ###### 4. Re: Conditionally format measure values as number vs. percentage

You're very welcome!

One thing you can try if there's an alignment issue is to make sure that the two fields are next to each other (and not separated by a line break) in the text editor.