8 Replies Latest reply on Feb 22, 2018 1:22 PM by Rajesh Agarwal

# Custom Format Decimal based on data

I'm looking to format a number field based on the size of the data.

For larger numbers (over 10) I don't want to show a decimal place. for numbers less than 10 and greater than 1 I want to show only one decimal place and for numbers less than one I want to show 2 decimal places.

example

150.92 would show 151

5.92 would show 5.9

0.92 would show 0.92

in excel I would use a conditional format

[<1]0.00; [<10]0.0, 0

This doesn't work in tableau.

I also tried a calculated field that rounded the values

if sum([NUMBER] )<1 then round(sum([NUMBER] ),2)

elseif sum([NUMBER] )<10 then round(sum([NUMBER] ),1)

else round(sum([NUMBER] ),0)

end

then entered a custom format of  #,##0.##

It almost worked,

but for whole numbers, it's showing the decimal. example 150.92 shows as  151.

Any Ideas how to get it to work?

• ###### 1. Re: Custom Format Decimal based on data

Try multiplying the whole number by -1. Then you can use the other side of the custom format pair to deal with the decimal. So your custom format would be:

#,##0.##;#,##0

(Note that's a colon in there in the middle.) Does that work?

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Custom Format Decimal based on data

Shawn,

It did work, very cleaver to convert the whole number to a negative then format negative numbers without a decimal.

Since some of my numbers are very small I was still having an issue with some showing up as  0.  so I added one more set of digits after the decimal so they would be represented.

My calculated field:

if sum([NUMBER] )<.01 then round(sum([NUMBER] ),3)

elseif sum([NUMBER] )<1 then round(sum([NUMBER] ),2)

elseif sum([NUMBER] )<10 then round(sum([NUMBER] ),1)

else round(sum([NUMBER] ),0)*-1

end

My custom format:

#,##0.###;#,##0

Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Custom Format Decimal based on data

Heather glad to help. You taught me a new trick on this one. I hadn't thought to use .#### and an IF/THEN to get different decimal lengths. I'll put that in my bag of tricks. Thanks.

--Shawn

• ###### 4. Re: Custom Format Decimal based on data

Shawn, thanks once again for your cleverness.  I was able to apply the conditional formatting based on the positive / negative notation.

• ###### 5. Re: Custom Format Decimal based on data

It's so cool when 5-6 month old threads get found and used! Jeffrey thanks for searching this out. And glad it helped.

Cheers,

--Shawn

• ###### 6. Re: Custom Format Decimal based on data

The way helps me a lot!!

Thanks!!

Hiroko.

• ###### 7. Re: Custom Format Decimal based on data

Hi Shawn,

I also have same issue. In one column, I have 2 types of numbers, one is count and another is sales. There is filter which has 2 values: Count and Sales. Based on filter selection, data is shown.

For count, we want to show full number without any decimal, but for sales, we want to round to million with 3 decimal places.

I have created a calculation as:

IF [\$/COUNT] = 'DOLLAR' THEN ROUND((AMOUNT/1000000),3)

ELSE AMOUNT

END

It does as required but for sales, but after decimal it shows full numbers, not limiting to 3 digits. when I went to field Number format and tried to format from there, then count is also getting formatted in the same way as sales.

Not able to use your trick of multiplying whole number with -1.

Thanks!

• ###### 8. Re: Custom Format Decimal based on data

I just got it.

ROUND(SUM(IF [\$/Count] = 'Dollar' THEN ([Amount]/1000000)

ELSE ([Amount])

END),3)

And it worked.

Thanks!