Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

pade
Advocate III
‎05-02-2021 12:04 PM

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:

Matrix presision3.png

And on-top of that, you can also give the user the possibility to choose how the formatting shall be done using standard filter

User defined dynamic formattingUser defined dynamic formatting

The alternative could have been one of the two formattings below:

Matrix 2aMatrix 2aMatrix 2bMatrix 2b

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

  1. Exported values (to excel or CSV) beeing reduced by 1'000, 1'000'000, etc
  2. 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:

Exported Excel FileExported Excel File

And maybe most importantly, using the method described here, diagrams like the bar-chart below will show the values formatted as you decide:

Bar Chart using Value Dynamic FormattingBar Chart using Value Dynamic Formatting

 

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.

 

Datamodel

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

Data ModelData Model

 

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:

Calculation GroupCalculation Group

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:

Matrix 3aMatrix 3a

 

Into this matrix using 3 digit precisions:

Matrix 3bMatrix 3b

 

Is to apply a filter on the Calculation Group to the visual, e.g. like below:

Applying a Calculation Item as a visual level filterApplying a Calculation Item as a visual level filter

 

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:

Applying a canvas filter to let the user define what formatting to useApplying a canvas filter to let the user define what formatting to use

 

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.

 

  1. 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 ",,,.".
  2. 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?).
  3. 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:

 

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

Temoprary work aroundTemoprary work around

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

  1. From the External Tools tab in Power BI Desktop, click on "Tabular Editor".
  2. In Tabular Editor, right-click on the Tables folder and choose "Create New" and then "Calculation Group".
  3. Give it the name "Sufix".
  4. 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

  1. Right-click on Calculation Items and choose (New Calculation Item), give it the name "None".
  2. In the editor window, write: SELECTEDMEASURE().
  3. 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

  1. 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.
  2. 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 ",.".
  3. 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.

  1. Save the data model by pressing Ctrl-s or push the save cube icon in the ribbon.
  2. 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.
  3. 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.
  4. Check the "Require Single Selection" checkbox and then select e.g. "Kilo".

First testFirst test

 

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:

Data sent between to the visuals using Calculation Groups formattingData sent between to the visuals using Calculation Groups formatting

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.

Dynamic formatting.pbix