Conditional Rounding

[aTChris]

Hi everyone

Can anyone think of a way that I can apply conditional rounding to a card?

I have a report showing revenue, profit, targets etc for different business units within a group. Some units are smaller than others, therefore, they report in hundreds of thousands some over a million. I want to be able to show one decimal place if millions but zero decimal places if thousands.

e.g. £562K & £1.3M

I use a measure to calculate the values which are then filtered by business unit thus applying the decimal place in modeling impacts all business units.

Hope someone can help.

Thanks

Chris

[amitchandak]

You can check the number use if or Switch and format to give a formatted number. But the output will become text

https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

if([Measure]>1000 && [measure]<=1000000,format([measure]/1000,"#,#")

[aTChris]

@amitchandak 

Thanks I will give that a go

[aTChris]

@amitchandak 

That is really useful, the only concern I have is the rounding. Using your suggestion I've got it displaying correctly.

Measure = IF([Measure]>1000000,FORMAT([Measure]/1000000,"£#.#M"),FORMAT([Measure]/1000,"£#K"))

However if the value is £309.5K it only shows £309, where I should display £310K

Do you have any suggestions for rounding before I convert to text?

Thanks

Chris

[amitchandak · Accepted solution]

Use round to in formula and check

Measure = IF([Measure]>1000000,FORMAT(round([Measure]/1000000,0),"£#.#M"),FORMAT(round([Measure]/1000,0),"£#K"))