
1. Re: Custom Format Decimal based on data
Shawn Wallwork Jun 24, 2014 3:41 PM (in response to Heather Fingalson)1 of 1 people found this helpfulTry 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

2. Re: Custom Format Decimal based on data
Heather Fingalson Jun 25, 2014 8:02 AM (in response to Shawn Wallwork)1 of 1 people found this helpfulShawn,
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.

3. Re: Custom Format Decimal based on data
Shawn Wallwork Jun 25, 2014 8:13 AM (in response to Heather Fingalson)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
Jeff Strauss Nov 4, 2014 9:30 AM (in response to Shawn Wallwork)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
Shawn Wallwork Nov 4, 2014 9:36 AM (in response to Jeff Strauss)It's so cool when 56 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
Hiroko Ogawa Dec 11, 2014 11:13 PM (in response to Heather Fingalson)The way helps me a lot!!
Thanks!!
Hiroko.

7. Re: Custom Format Decimal based on data
Rajesh Agarwal Feb 22, 2018 1:12 PM (in response to Heather Fingalson)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.
Please help.
Thanks!

8. Re: Custom Format Decimal based on data
Rajesh Agarwal Feb 22, 2018 1:22 PM (in response to Rajesh Agarwal)I just got it.
ROUND(SUM(IF [$/Count] = 'Dollar' THEN ([Amount]/1000000)
ELSE ([Amount])
END),3)
And it worked.
Thanks!