Creating Value Dynamic Formatting using Calculation Groups
What you can do and why you should
Have you ever been facing the problem of showing small and large numbers together (e.g. 123,456, 789.99 and 34.56) in a nice way, lets say with a 3 digit precison as 34.5 and 123G (or 123E6).
When working with large numbers you can divide them with 1,000 or 1,000,000 and round them to zero (or two decimal points), but then small values could be presented as “0”.
You can also add bars and colors to the background (or text colors) reflecting the size of the values… But usually when you have a design you think will work, the user will filter down the data to something you haven’t expected and then again, things are not showing as you and your users would expect. So all work for nothing.
Wouldn’t it be nice to be able to show values like this, in this case using a 3 digit precision:
And on-top of that, you can also give the user the possibility to choose how the formatting shall be done using standard filter
The alternative could have been one of the two formattings below:
As can be seen above:
- In Matrix 2a, not reducing the number of digits makes it hard to read, even though I’m using background color and thousand separators.
- In Matrix 2b values less than 10’000 are “hidden” as 0.00M.
Note that simple user-defined formatting in kilo, Mega, etc, using a filter has been possible before, but with some side-effects like
- Exported values (to excel or CSV) beeing reduced by 1’000, 1’000’000, etc
- Hovering over values in diagrams will show values reduced with 1’000, 1’000’000, etc.
Both (1) and (2) above might in some cases be the intention, but then the user needs to be aware of what the numbers means.
The method described here doesn’t have the side-effects above, i.e. for side-effect (2), see the exported Excel below, keeping the exported numbers at their original precision:
And maybe most importantly, using the method described here, diagrams like the bar-chart below will show the values formatted as you decide:
This (and more) is possible using advanced formatting string and some simple DAX in Calculation Groups.
Microsoft has already described how to do dynamic formatting, and the SQLBI guys has very good articles as well (here and here). This post goes beyond that and shows how to format dynamically even based on the values.
Note/Warning: At the time of writing, some bugs exist in the standard visuals for Power BI, but Matrixes and Multicard visuals seams to work and should be safe to use (chart diagrams are not ok even though I have showed one chart diagram above). The bugs are confirmed by Microsoft although no ETA has yet been confirmed. External ticket number is “2104200050002054” if someone would like to refere to it. I have also created a idea to fix the formatting issues, please vote.
Implementing in your reports
Implementing Value Dynamic Formatting requires a Calculation Group. But I will start with showing how this can be implemented in the reports if you alread have this Calculation Group created. The reason for this is that creating Calculation Groups requires 3’rd party tools (i.e. the Tabular Editor) and this could be seen as a bit complex if you haven’t done it before. So instead, please use my already prepared PBIX file and determin if this is for you. For information on how to create the needed Calculation Group, please see “Creating the Calculation Group” below.
The datamodel used in this example is very simple, a super small Fact table, one Calendar dimension an a Dim Name table (this could also have been just one flat table). But most importantly, there’s also a disconnected table called Sufix, and this is a table of the special type “Calculation Group”.
What’s an Calculation Group
A Calculation Group looks just like a regular table, having two columns. One index column (called “Ordinal”) controling the sort ordering, and one content column (in this case renamed to “Sufix”). Each row in the Calculation Group represents a Calculation Item (i.e. “Kilo”, “Million”, “Precision 3”, etc). Using the data view in Power BI the Calculation Group table looks like below:
For now, we can see each row in the Calculation Group (i.e. each Calculation Item) as something capable of overiding explicit measures with new code, and owerwriting existing formatting with new formatting. In simple cases like in “Kilo”, the Calculation Items overrides the measures with a formatting of “#,0,.##k$”, but in case of “Dynamic1”, “Precision 3”, etc, it’s more complex codes involved. The only thing we need to know at this point in time is that the sequence of “,.” in the formatting string (a comma just before the decimal point) means divide with 1,000 before displaying. The same way a sequence of “,,.” (two commas just before the decimal point) means divide with 1,000,000 before displaying, etc.
The usage of Calculation Groups to format values
To activate this formatting we simply filter a Calculation Item (i.e. a single row from the Calculation Group) for a visual. This can be done as either a visual level filter, a cross filter on the canvas, a page level filter, or a report level filter. This can also be done explicitly within a measure, but that is not so relevant in this case.
So as of now, the only thing you need to do when changing this original matrix:
Into this matrix using 3 digit precisions:
Is to apply a filter on the Calculation Group to the visual, e.g. like below:
To have the formatting affecting all visuals on the canvas, just place the Calculation Item as page level filter. To let the user decide how to format, place the filter as a visual filter on the canvas like below:
Some notes about what configurations has been needed for the page above.
- I have intendendly turned of the interaction from the filter visual to the two lower matrixes (marked with yellow in the diagram above). For these matrixes I have instead in the left matrix used the built-in Field Formatting setting to change the Display unit to Millions.
- Please also note that for all visuals affected with this formatting Calculation Group, you need to make sure that the “Display unit” configuration setting is set to “None”. Sometimes this setting has a default value of “Auto” and this will cause problems sometimes hard to see before releasing the report.
You find this setting under different setting categories depending on the visual, e.g. under “Field formatting” for matrixes and under “X/Y axis” as well as under the “Legend” settingsfor bar/column charts.
But you also need to know this
That was almost everything you need to know about using Calculation Groups to format values in your visuals, but there are things to point out.
- The Y-axis scale does not work (marked with red in the picture above). It’s showing 1,000,000,000G instead of simply 1G. This is due to confirmed bug. The axis gets the “G” from the formatting but forgets to divide the value with 1’000’000’000 as it should since the formatting string contains “,,,.”.
- The legend value for Steve is not correct (marked with red in the picture above). It shows 123457k instead of 123k. There’s many fault here since the raw value is 123’456.70. (where’s the “6” and where’s the decimal point?).
- The tooltip value shown when hovering (marked in green) is correct even though the lgend values are not, see (2).
Safe usage untill the bugs are fixed
For Multiline card visuals and Matrix visuals I have not seen any bugs, so I woud consider them safe to use. For the other visuals we can still benefit a bit from this formatting method, and there are two methods I would recomend:
- Turn of Y-axis and legend values in your bar/column chart visuals (and also set the “Display unit” settings to “None”). Since it’s only the Y-axis and the legend values that are not showing correct values, the diagrams will still show the correct size of the bars and the corect formatted values for the tooltips.
- As an alternative, if showing the Y-axis (and/or showing the legend values) is a requirement, you can create an explicit measure, maybe called “measure1_format” and make sure that the Calculation Item formatting only affects this measure. This can be done by adding code to the Calculation Group formatting to only affect measures ending with _format. Then add this measure as an explicit tooltip measure. Now the diagram will work like it did before you started to use the Calculation Groups, but with the addition that the tooltip will be formatted in a better way.
Creating the Calculation Group
Disclaimer: Before working with Calculation Groups, make sure you read and understand what it means, and make sure you take a backup of your PBIX file. Everything described here is safe, but you will be dealing with 3’rd party tools not supported by Microsoft, although the API the tools are using are supported by Microsoft.
Creating a Calculation Group requires for now the 3’rd party tool called Tabular Editor. The SQLBI guys has some very good articles on what Calculation Groups are and how to create them using Tabular Editor, so I will not dig that deep into that. Instead I will create a step-by-step instruction for how to create the calculation items used in this post, and a pre-requsit is that the Tabular Editor is installed.
Step 1. Create a Calculation group
- From the External Tools tab in Power BI Desktop, click on “Tabular Editor”.
- In Tabular Editor, right-click on the Tables folder and choose “Create New” and then “Calculation Group”.
- Give it the name “Sufix”.
- Expand the Calculation Group and rename the column called “Name” to “Sufix”.
You have now created an empty Calculation Group and renamed the column to “Sufix”.
Step 2. Create the Calculation Items
- Right-click on Calculation Items and choose (New Calculation Item), give it the name “None”.
- In the editor window, write: SELECTEDMEASURE().
- Repeat the same steps (1) and (2) for the calculation items “Kilo”, “Million”, and “Precision 3”.
You have now creted the four Calculation Items “None”, “Kilo”, “Million”, and “Precision 3”, and configured them with the DAX code SELECTEDMEASURE(). This will make sure that the Calculation Group will work for all explicit measures. Without the SELECTEDMEASURE(), you will only see blank values.
Step 3. Configure the formatting for the simple Calculation Items
- Klick on Kilo and in the preference dropdown window in the editor windows, choose “Format string expression” instead of “Expression”. Now you can write the formatting code.
- Write exactly this, including the double quotes: “#,0,.##k”
This will format the values with thousand separators, and with two possible decimal digits and add a “k” after all values. This will also divide the value with 1’000 due to “,.”.
- Now do the same but for the Calculation Item “Million”. Use this exact formatting string: “#,0,.##M”.
You have now configured the two simple Calculation Items with the formatting string. The Configuration Item “None” does not need a formatting string, so if configured, you could also just add the DAX-code BLANK() instead of not configuring it, the result will be the same.
Step 4. Test the three configured Configuration Items
Before proceeding with the more complex Configuration Item “Precision 3”, we should test what we have done.
- Save the data model by pressing Ctrl-s or push the save cube icon in the ribbon.
- Switch back to Power BI Desktop and push the “Refresh now” button. You see this button since you added/changed a Calculation Group and the Calculation Items.
- You now see the newly created Calculation Group besides your other tables. Just add the “Sufix” column from the “Sufix” Table (Calculation Group) as a visual level filter as in the picture below.
- Check the “Require Single Selection” checkbox and then select e.g. “Kilo”.
Test both “None”, “Kilo”, and “Million” to make sure all works. Also add the Calculation Item as a filter on the canvas, but then also remember to remove the same as a visual level filter.
Step 5. Configure the formatting for the “Precision 3” Calculation Item
Repeat the previous steps for creating the three simple Configuration Items, but this time use this code as the “Format string expression”:
VAR V = ABS( SELECTEDMEASURE () ) RETURN SWITCH ( TRUE (), V >= 1E10, "0,,,.0G", V >= 1E9, "0,,,.00G", V >= 1E8, "0,,.M", V >= 1E7, "0,,.0M", V >= 1E6, "0,,.00M", V >= 1E5, "0,.k", V >= 1E4, "0,.0k", V >= 1E3, "0,.00k", V >= 1E2, "0.", V >= 1E1, "0.0", V >= 1E0, "0.00", "#,0.00" ), BLANK () )
This is a bit more complex formatting code, but still very simple. Depending on the absolute value returned from the explicit measure, different formatting strings will be used, all delivering a 3 digit precision.
To test this, repeat the steps you used when testing the three other Configuration Items
Note: The formatting codes showed hera are a bit simplified compared with the code you would used in your report. In your report you would like to protect the Calculation Group so it doesn’t “fire” for unwanted measures. Analysing the PBIX file in this blog you will see how that could be done, but below you see one simple example that only “fires” the formatting whenever the measure name starts with “Sum “:
IF ( SEARCH ( "Sum ", SELECTEDMEASURENAME (), 1, 0 ) = 1, "#,0,.##k$", BLANK () )
Calculation Group Precedence
Please also note that more than one Calculation Group can be applied on one single visual and you can/must control the execution order. As I can’t think of any Calculation Group that you would like to apply before the formatting Calculation Group (i.e. you would like this to be the most outer CALCULATE() when the applied Calculation Groups are re-written) I strongly recomend to set the Calculation Group’s Precedence to 10’000 or higher.
Behind the scene
To understand more what’s happening when applying the formatting strings using Calculation Groups, we can use the tool “DAX Studio” provided by SQLB.com. Using this tool we can see that for e.g. the bar chart diagram, the data sent to the visual looks like below:
As can be seen above, the formatting string is added as an extra column to all rows. If more than one values was to be shown in the visual, one extra column will been added for each value columns. Note that since I’m here using the “Precision 3” dynamic method, the formatting is specific for each row.
Read more at: Community Power BI
Leave a ReplyWant to join the discussion?
Feel free to contribute!