Card values with conditional decimals

[Anonymous]

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:

IF([Measure]>1000000,FORMAT(round([Measure]/1000000,1),"$#.#M"),FORMAT(round([Measure]/1000,1),"$#.#K")))

The problem here is that if the total is 25000, the measure shows up as "25.K", or with the 2000000 total, "2.M", leaving the decimal there regardless of whether it's needed. Is there any fix for this at all, either in the formula within the measure or with the formatting of the card itself?

[Anonymous · Accepted 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:

Here is the pbix file.

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

[Anonymous]

Thank you, Evelyn!

[MattAllington]

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. 

[Anonymous]

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.