10 Replies Latest reply on Mar 11, 2016 5:34 AM by Sreehari Katageri

# Conditional Number Formatting

Hello all,

Is it possible to change the number of digits after a decimal point based on the value. Essentially, if the result is <100 I want a decimal point, if it is >100 I don't want one. I know I could do this with different sheets and a parameter, but that really isn't feasible because there are multiple metrics on the sheet all independent of each other.

I don't think there is a way to do this, but maybe someone has an idea!

Thanks

• ###### 1. Re: Conditional Number Formatting

Hi Brian,

maybe you try this:

IF([Profit] < 100)

THEN REPLACE(str(ROUND([*YOUR-VALUE*],1)),".",",")

ELSE

REPLACE(str(ROUND([*YOUR-VALUE*],0)),".",",")

END

Sincerely,

Kevin

• ###### 2. Re: Conditional Number Formatting

Hi Brian!

You can create a calc like:

Calculation 1: if sum([Profit]) > 100 then 'More' elseif sum([Profit]) < 100 then 'Less' end

Calculation 2: if [Calculation1] = 'More' then sum([Profit]) elseif [Calculation1] = 'Less' then -sum([Profit]) end

Notice the (minus) sign in the elseif condition. This will allow you to custom format negative numbers in a different way. Then right click on 2nd calc int he measure pane choose default properties > number format > custom. Enter this in the custom format box: So glad I got to use a nice trick taught to me by my fellow Ambassador Shawn Wallwork

2 of 2 people found this helpful
• ###### 3. Re: Conditional Number Formatting

OR actually you can just create 1 calculated field:

if sum([Profit]) > 100 then sum([Profit]) elseif sum([Profit]) < 100 then -sum([Profit]) end

Then right click on this calculated field and use the custom formatting I explained above

1 of 1 people found this helpful
• ###### 4. Re: Conditional Number Formatting

Thanks Pooja, this is an interesting solution, but it doesn't work if negative values are a legitimate value?

In my example I am dealing with growth rates, which can be positive or negative so this solution essentially turns the negative values positive in addition to removing the zeros.

• ###### 5. Re: Conditional Number Formatting

Thanks Kevin, that would work for single values, but then you remove the ability roll up the data because they are strings.

• ###### 6. Re: Conditional Number Formatting

Oh I see. You can change the formula to accommodate negative values in that case:

if sum([Profit]) > 100 then sum([Profit]) elseif sum([Profit]) < 100 then -(-sum([Profit])) end

In the custom formatting then, have it display the negative signs also: Now you will notice that the negative values are retained as in the original profit measure with 2 decimal points. Does that work?

1 of 1 people found this helpful
• ###### 7. Re: Conditional Number Formatting

So that fixes the negative issue, but based on your output it looks like it is only working for the negative values now. It would need to work for both negative and positive values.

Results should look like:

65.33

-23.55

1000

-144

• ###### 8. Re: Conditional Number Formatting

Oh yes, that's right! You have a mixed condition for positive numbers (some need decimals and some do not based on if they are greater than 100 or lowers than a 100). Our custom formatting took care of 2 settings (negative or positive). So all positives will not get a decimals and all negatives do but because you need some positives to get a decimal, I think converting the numbers to a string would be your only alternative at this point. Are you just displaying these numbers somewhere on the view or actually using them for like grand totals etc?

Discrete values can not be used for grand totals, so if you convert the numbers to text, that might be an issue. Also which data source are you connecting to? Which version of Tableau are you using? Are you connecting live or using a data extract?

1 of 1 people found this helpful
• ###### 9. Re: Conditional Number Formatting

Yeah I think the string would be the only way, which doesn't exactly work in my case because I need them to be rolled up in totals, but it would work if they are displayed on their own.

I am connecting to a local excel data source with 9.2 and pulling an extract.

Thanks for your help!

• ###### 10. Re: Conditional Number Formatting

Hi ,

I want the following number formatting in custom number format

if number is 10,000 then it should be 10,000

if number is 100,000 then it should be 1,00,000

if number is 1,000,000 then it should be 10,00,000 etc .....

custom format should be dynamic like below excel formula

[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0