Dynamically converting numbers and showing in donut or pie charts is returning blank

[Krishnan_47]

Hi All,

I am dynamically converting numbers in a measure into showing Millions or Thousands. If a number is large, we will show 'M' besides the number and if the number is small we will display 'K'. Please find below screenshot.

Now when I use this measure in a pie chart or donut, it shows as blank.

I would like to know if there is any other way to display dynamically converted numbers into pie chart or donut? I am trying for the ouput as shown below:

[Anonymous]

Hi @Krishnan_47 ,

You can set the display unit to be 'K' or 'M', but not both 'K' and 'M' in the same field.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You can do something like this:

Or you can get a bit fancier and use logarithms.

FormattedMeasure = 
VAR _Value = [UnformattedMeasure]
VAR _Digits = IF ( ABS ( _Value ) < 1, 0, LOG10 ( ABS ( _Value ) ) )
VAR _Commas = ROUNDDOWN ( _Digits  / 3, 0 )
RETURN
    SWITCH (
        _Commas,
        0, FORMAT ( _Value, "0" ),
        1, FORMAT ( _Value, "0,k" ),
        2, FORMAT ( _Value, "0,,M" ),
        3, FORMAT ( _Value, "0,,,B" ),
        FORMAT ( _Value, "#,0,,,,T" )
    )

For more on the FORMAT function, see here (particularly the last couple of examples):
https://dax.guide/format/

[Krishnan_47]

Hi Alexis Olson,

Thank you for the reply. I need to use this measure inside a Pie Chart or a Donut. Currently it is displaying as blank.

[AlexisOlson]

That's because this outputs text but charts need numbers.

I think you can get around this using a format string expression as part of a calculation group.

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/