Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Use round to in formula and check
Measure = IF([Measure]>1000000,FORMAT(round([Measure]/1000000,0),"£#.#M"),FORMAT(round([Measure]/1000,0),"£#K"))
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,"#,#")
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
Use round to in formula and check
Measure = IF([Measure]>1000000,FORMAT(round([Measure]/1000000,0),"£#.#M"),FORMAT(round([Measure]/1000,0),"£#K"))
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |