12 Replies Latest reply on May 26, 2016 11:04 PM by Aman Agrawal

# How to remove decimal values from calculated fields

Hi ,

I have created a calculated field. Given below calculated field:-

if [KPI]= 'CS' then AVG([CS])

ELSE IF  [KPI ]= 'Attempts' then SUM([Attempts])

end

Now, in CS values is showing like 12.222

I want to show decimal values up to two digits only.

If i am using Default number format --> Custom then it changes all the CS into 12.222 and Attempts like - 13.00

I want CS into 12.22 format(up to two digits)

and Attempts into 13 format.(Not required in digits)

Using some formula in calculated field..

Thanks

Aman

• ###### 1. Re: How to remove decimal values from calculated fields

The ROUND formula should do it.

if [KPI]= 'CS' then ROUND(AVG([CS]),2)

ELSE IF  [KPI ]= 'Attempts' then ROUND(SUM([Attempts]),0)

end

• ###### 2. Re: How to remove decimal values from calculated fields

Hi Aman,

One quick thing, you're going to get an error because you're comparing a non-aggregating field (KPI) in a calculated field that has aggregated fields (AVG and SUM). Wrap KPI in MIN() or MAX() and that will take care of that problem.

The question you're asking is a tough one and not as simple as changing the default number format, because you're wanting two different formats for a single field (albeit a calculated field). Here's an idea, create a calculated field for your Label and use that on your view.

For instance, try something like this:

IF [KPI]= 'CS'

THEN STR(ROUND(AVG([CS]),2))

ELSEIF [KPI]= 'Attempts' THEN STR(INT(SUM([Attempts])))

END

And then drag it onto labels. Hope this helps!

Robert

• ###### 3. Re: How to remove decimal values from calculated fields

Hi Robert,

Sorry, but this is not working

is there any other solution you know please provide me.

Thanks

Aman

• ###### 4. Re: How to remove decimal values from calculated fields

Hi Watson,

This solution is not working.

Please share some other solutions that you know.

Thanks

Aman

• ###### 6. Re: How to remove decimal values from calculated fields

Sorry Robert, I can't due to some security reasons.

• ###### 7. Re: How to remove decimal values from calculated fields

Here's my solution, but using a different data source then:

It works, just takes a bit of manipulation with the labels as we have to convert them to discrete to bypass the formatting.

• ###### 8. Re: How to remove decimal values from calculated fields

Hi Robert,

I am unable to open your image that you shared with me.

Thanks

Aman

• ###### 9. Re: How to remove decimal values from calculated fields

You can also set the default format of your measures like this.

I've duplicated the sales measure and kept the original measure as it is and changed the default format of copied measure to two decimals as per the above screen shot.

See the difference.

Mahfooj

• ###### 10. Re: How to remove decimal values from calculated fields

Hi Mahfooj,

• ###### 11. Re: How to remove decimal values from calculated fields

To progress on this one you'll need to give more information, at least a sample dataset so the structure can be understood.

• ###### 12. Re: How to remove decimal values from calculated fields

Hi Everyone,

Thanks for your quick response. But, now i have solved the issue.

Thanks

Aman