Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm having issues with a few measures I've created where visually, they are not being formatted the way I want them to be. Given specific values calculated by the measures, I need them to show up as such:
Measure = 25000, Card shows "25K"
Measure = 25100, Card shows "25.1K"
Measure = 2000000, Card shows "2M"
Measure = 2100000, Card shows "2.1M"
Right now, this is what I have as a rounding and formatting mechanism within the measure:
Solved! Go to Solution.
Hi @Anonymous ,
You could use the following formula:
Measure 2 =
VAR _countof0 =
LEN ( [Measure] ) - LEN ( SUBSTITUTE ( [Measure], "0", "" ) )
RETURN
IF (
_countof0 <= 2,
FORMAT ( ROUND ( [Measure] / 1000, 1 ), "$0.0" ) & "" & "K",
IF (
_countof0 > 2
&& _countof0 < 5,
FORMAT ( ROUND ( [Measure] / 1000, 1 ), "$#" ) & "" & "K",
IF (
_countof0 = 5,
FORMAT ( ROUND ( [Measure] / 1000000, 1 ), "$#.0" ) & "" & "M",
IF (
_countof0 > 5
&& _countof0 < 8,
FORMAT ( ROUND ( [Measure] / 1000000, 1 ), "$#" ) & "" & "M"
)
)
)
)
My output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @Anonymous ,
You could use the following formula:
Measure 2 =
VAR _countof0 =
LEN ( [Measure] ) - LEN ( SUBSTITUTE ( [Measure], "0", "" ) )
RETURN
IF (
_countof0 <= 2,
FORMAT ( ROUND ( [Measure] / 1000, 1 ), "$0.0" ) & "" & "K",
IF (
_countof0 > 2
&& _countof0 < 5,
FORMAT ( ROUND ( [Measure] / 1000, 1 ), "$#" ) & "" & "K",
IF (
_countof0 = 5,
FORMAT ( ROUND ( [Measure] / 1000000, 1 ), "$#.0" ) & "" & "M",
IF (
_countof0 > 5
&& _countof0 < 8,
FORMAT ( ROUND ( [Measure] / 1000000, 1 ), "$#" ) & "" & "M"
)
)
)
)
My output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Thank you, Evelyn!
Try turning off the auto formatting in the card. I don't remember where it is, I think it is on one of the top few format menus.
I tried doing that and still had the same issue with the decimal point remaining. I would actually use the "Auto" feature, but they switch between none and two decimal places, not one, which is what I'm trying to accomplish.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |