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 ...
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

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!