Format number/currency to decimal places in dataview

What I’m trying to do

I want a currency column in my dataview table to display 2 fixed decimal places. Currently, the column (cost) looks like this:

What I want to achieve is the data to be displayed this way:

abc 12.00
abc 48.06
abc 210.00
abc 12.10

Things I have tried

I’ve tried to use functions or lambda for the fields section in dataview without any success - there must be a simple solution.

    formatnum(cost, "#.##")

Can anyone tell me a workaround?

So finally, I’ve come up with the following (ugly) solution:

    choice(contains(string(cost), "."), choice(regextest("\.\d\d", string(cost)), cost, cost + "0"), cost + ".00") as "Cost"

I think you will have to live with that since

round(number, [digits]) doesn’t really work for some numbers.
12.00 is shown as 12

The formatting of numbers are a little limited, but various hacks do exist. Here is one to display cost with two decimals:

regexreplace(string(round( cost *100)), "(..)$", ".$1")

It’s easily customised to one decimal, and this trickery will work as long as your numbers are not too large or the number of decimals is too large.

1 Like

Thank you very much. Looks like a good and simple solution.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.