3 Replies Latest reply on Aug 12, 2016 10:32 PM by Kurt Heisler

Conditionally format values with varying decimal levels

I have data showing data on various indicators, often by subgroups (like performance by ***, race, etc.). All of the values are stored in a single field called Value. Some of the original values have no decimals, some have 1, and some have 2.

The underlying data look like:

I'd like to create a table like below that shows the values with their actual value (i.e., down to the decimal level and not rounded). Obviously, I can apply only one format to Values at a time.

How can I conditionally format these based on the level of decimals stored in the underlying data?

In the underlying data, I did add variables for DataFormat (Number, Percent) and Decimal (0,1, or 2). I'm okay not showing "%s" for the Percents if that's too tricky, so maybe the Decimal field is all that's needed.

The solution I've come up with in a similar situation is to use a calculated field that returns a string, [Value2 Format % or N], in place of Value.

Value2 Format % or N:

IF attr([DataFormat]) <> "Number"

THEN str(int(mid(str(round(min([Value2]),3)),3,2))) + "." + str(int(mid(str(round(min([Value2]),3)),5,1))) + "%" else str(round(min([Value2]),0))

END

But in this case I think need something like:

IF attr([Decimals]) = 0

THEN // show original values, and format number with commas in thousands place (e.g., "1,000")

IF attr([Decimals]) = 1

THEN // show original values, and format with one decimal (e.g., "92.6")

IF attr([Decimals]) = 2

THEN // show original values, and format with two decimals (e.g., "60.75")

END

Any suggestions? Thank you.

• 1. Re: Conditionally format values with varying decimal levels

Hi Kurt, It would be a lot simpler if you had separate measures for each of your indicators: Passing Test, Rate of Exit, etc. You could create calculated fields for each. For example, you could build the Passing Test (number) calculated field like this: IF [Indicator] = 'Passing Test (number)' THEN [Value] END. Then you could format each measure individually.

It looks like you're doing some aggregation to this excel file before importing it into Tableau. If you can plug Tableau into the raw data, you can do a lot of this work within Tableau too. Things like creating the percentages, totaling within each category, etc.

• 2. Re: Conditionally format values with varying decimal levels

I have hundreds of indicators in the full data, so I don't think creating separate measures for each indicator would be manageable. Hmm ...

• 3. Re: Conditionally format values with varying decimal levels

Wait. I can't believe this.

Set the format for Value = Number (Standard).

1 of 1 people found this helpful