Dynamic formatstrings with Calculation groups

One of the cool things of calculation groups is they cannot just be used to apply a calculation over your “base measure” but also apply a dynamic formatstring. You could do this without calculation groups before too as I described here. This method had one big drawback though, it uses the FORMAT function and when doing that all results are transformed into strings. This does help in showing the right format, but you lose a lot of other functionality like sorting or conditional access. Now with calculation groups we can do custom formatting and keep the data type. How does that work?

First, we add our calculation group to the model. The steps to adding calculation groups I described in this previous blog post here. For my new example I want to use calculation groups to change formatting for two cases, one that changes the format per promotion group and one that changes based on the granularity of my selection.

I add the format calculation group and two calculation items called “Format promotions” and “Format granular”

Let’s look at the first one “Format promotions”, I don’t want to add any special calculation to the current expression, so I just use SELECTEDMEASURE to just return the current measure in context:

=SELECTEDMEASURE ()

What I do want is to be able to change the formatstring based on the context. Calculation groups allow you to change the format based on an expression. So, in my case I want to return a different formatstring based on the promotionkey selected. The formatstring expression part of the calculation item expect a string returned in VB format. I can write something like this:

=
IF (
    SELECTEDVALUE ( ‘DimPromotion'[PromotionKey] ) < 10,
    “#,##0”,
    IF ( SELECTEDVALUE ( ‘DimPromotion'[PromotionKey] ) < 20, “#,##0.00”, “$#,##0” )
)

When I now look at the sum of quantity by promotionkey I don’t see anything yet as we haven’t applied the calculation group to this visual yet.

Now when I apply the calculation group by selecting the calculation item using the filter pane you will see the formatting gets applied.

Of course, this is just an example, but you can change it to whatever you want. The main difference between the previous blog post and this one is that you can still sort by Sum of Quantity, it is still a number and not text.

Another interesting one would be if we could change the formatstring based on the granularity of the selection. Again, the expression would be:

=SELECTEDMEASURE ()

But the interesting part is again in the format string expression.

=
IF ( ISFILTERED ( ‘DimPromotion'[PromotionKey] ), “#,##0.00”, “$#,##0” )

Here I check if there is a filter on a single promotionkey and if so, I would format with two decimals, if not zero decimals. This looks like this in the visual:

Here you can see the difference between grand total when individual rows are showing the same measure.

This trick can open many interesting scenario’s and tricks, in my case I set the filter on the visual but I could also set it on the page where it now gets applied to all visual in my report. Now there is one drawback right now is that this doesn’t work for all visuals yet, most importantly charts. This is something the team is working on and should be fixed in the next few months. This has been delivered in the July Power BI desktop update.