Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Card values with conditional decimals

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?
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you, Evelyn!