Value formatting #2

[pade]

In 2021 I created a blog post here how to create dynamic formatting using Calculation Groups to format values dynamically, but at that point in time the there was a lot of problems with the visuals and the post got messy. The SQL BI guys also created an article addressing the same problems with the standard visuals: Controlling Format Strings in Calculation Groups).
I created a ticket to Microsoft asking them to update all their visuals and that has now been done (partly). There are still some problems but

1. They will hopefully soon be solved (please support me in getting attention on my new ticket #2405300050000333)
2. The problems are much minor compared to before.

So, time for an update with some new features as well

For those new to Calculation Groups and Formatting using Calculation Groups it's recommended to start with reading the SQL BI guys article mention above but you can also format without using calculation groups as described here: Create dynamic format strings for measures.
The two alternatives address two different ways of controlling the value formatting but will affect your visual in the same way. So, choose Calculation group whenever you want to have the same formatting for many measures and choose dynamic format string whenever you want to format a specific measure.

This post is all about how to show values with e.g. 3 significant figures like 9.5TB, 123GB, 567MB, 789kB, 32B etc in the very same diagram/table like below.

The only thing needed is a formatting string looking like this:

    VAR V =
        ABS ( SELECTEDMEASURE () )
    RETURN
        SWITCH (
            TRUE (),
            V >= 1E11, "#0,,,.bn",
            V >= 1E10, "#0,,,.#bn",
            V >= 1E9, "#0,,,.##bn",
            V >= 1E8, "#0,,.M",
            V >= 1E7, "#0,,.#M",
            V >= 1E6, "#0,,.##M",
            V >= 1E5, "#0,.K",
            V >= 1E4, "#0,.#K",
            V >= 1E3, "#0,.##K",
            V >= 1E2, "#0.",
            V >= 1E1, "#0.#",
            V >= 1E0, "#0.##",
            V >= 1E-1, "#,0.###",
            "0.##e-##"            
        )

And if used within a calculation group you might need to limit the formatting by encapsulating it within this code:

IF (
    ISSELECTEDMEASURE (
        [Measure1], 
        [Measure2],
        [Measure3] //etc
    ),
    "" //Your formating code goes here,
    SELECTEDMEASUREFORMATSTRING ()
)

The alternative would have been to have no formatting at all like this:

Or to choose a static formatting like below in "Mega":

So now let's look at the still existing problems.

As you might have noticed I have used logarithmic scaling and not even shown the Y-axis to easier show the bars in the bar chart but now I will switch to linear scaling and show the y-axis. At first this looks ok as below:

But... Changing the sort order of the bars we see that the formatting of the Y-axis is picked only from the first bar (not for the largest bar as it does when not using custom formatting):

The other problem is kind of very small but still significant if having requirements on how the results shall be presented, e.g. for an annual report.

The problem is that the graphical visuals doesn't read the formatting string correctly. A "#" means an optional digit wile a "0" means a mandatory digit. So, for the value 1201.11111 with the formatting string "#,0,.##K" the result shall be shown as 1.2K and not as 1.20K. As can be seen in the picture below, the formatting is correct in the table but not in the barchart:

But despite these two problems I have started to use dynamic formatting as often as I can since the figures makes more sense to me and my users. The most critical problem is the formatting of the values on the Y-axis and sometimes customers opt out on the dynamic formatting due to this.

And now to the extra extra feature.

As can be seen in all barcharts I have created a dynamic title by concatenating the text "The total is: " with the total value.

In this example it's a title but it could have been a card visual or anything else where we would like to combine the value with another text. The challenge is to get the value formatted according to the formatting rules. The only one caring about the formatting string generated is the visual and not arbitrary measures. So, when concatenating the string with the value generated by [Measure1] we need to apply the formatting ourselves.
Yes this could be done by applying the formatting code every time we concatenate the text like below but that would mean that we have the formatting code in more than one places.

Text = 
VAR FormatString = 
  VAR V = 
    ABS(SELECTEDMEASURE())
  RETURN
    SWITCH(
      TRUE(),
      V >= 1E11, "#0,,,.bn",
      V >= 1E10, "#0,,,.#bn",
      V >= 1E9, "#0,,,.##bn",
      V >= 1E8, "#0,,.M",
      V >= 1E7, "#0,,.#M",
      V >= 1E6, "#0,,.##M",
      V >= 1E5, "#0,.K",
      V >= 1E4, "#0,.#K",
      V >= 1E3, "#0,.##K",
      V >= 1E2, "#0.",
      V >= 1E1, "#0.#",
      V >= 1E0, "#0.##",
      V >= 1E-1, "#,0.###",
      "0.##e-##"
RETURN
  "The total is: " & FORMAT ( [Measure1], FormatString )

The trick is to be found by analysing what query PBI generates for the visual using custom formatting. E.G. the barchart is generated by the below code (after some clean up)

SUMMARIZECOLUMNS(
	'Table'[Decimal],
	TREATAS( { "Dynamic" }, 'Formatting rules'[Formatting] ),
	"Measure1", 'Table'[Measure1],
	"v_Measure1_FormatString", IGNORE('Table'[_Measure1 FormatString])
)

 That genereates this table:

What we can see is that one extra column is generated with the formatting code and looking into what measure is used we can see the measure [_Measure1 FormatString]. I.E. for all our measures that has a custom format we can get the generated formatting by simply using the measure [_MyFormattedMeasure FormatString]. So putting this in action we can simply write:

Text = "The total is: " & FORMAT ( [Measure1], [_Measure1 FormatString] )

The only problem is that Power BI Desktop doesn't allow us to use this "hidden" measure, but we can still implement it using tools like Tabular Editor. When viewing the measure in Power BI Desktop it will look like below with a red line indicating an error and I like to put a comment to make it understandable for other developers:

And as a last comment. In tables it might be harder to distinguish between large and small values when all values are written with 3 digits but adding bars or colors like below will adress that issue:

Number formatting.pbix