So today I recevied a question on the SML group which looked like the perfect use case for a calculation group. Somebody was trying to build a matrix and wanted to have the values without decimals but the totals with decimals. In other words the goal was defining different formats for values and totals. Without calculation groups, the only way you can get close to that is by using the function FORMAT, but that’s a dirty trick that will come back and get you because now your values are text. If you later use that measure in a SUMX expression for instance, it will not recognize it’s a number anymore. Of course you could keep a measure for the value and a different measure for the formatted value, but hmmm then you would have to do that for all measures that you might want to use this way and well, you would have to manage this sprawling number of measures. Let’s check both ways in case someone has external tools blocked or something.
Dirty Daxing with a measure
If we work in an evil corporation that allows no external tools, we’re stuck with a workaround to achieve this dynamic format string we need. We’ll use Contoso model as usual, and we can use [Sales] as our base measure. In our Matrix we are using the Manufacturer and Year as slicing dimensions.
How can we detect if the calculation takes place in a value cell or in a total cell? The answer is the function ISINSCOPE. In this function we need to provide a column and it will return true if the column values are slicing the calculation and false otherwise. In the value cells both Manufacturer and Year are slicing. In totals either is not slicing, or none at all for the grand total. So a first attempt to this Formatted Sales measure would look like this:
Formatted Sales = IF ( ISINSCOPE ( 'Product'[Manufacturer] ) && ISINSCOPE ( 'Date'[Year] ), FORMAT ( [Sales], "$ #,##0" ), FORMAT ( [Sales], "$ #,##0.00" ) )
However, this approach results with this
As we can see, the measure approach is a terrible idea. We are getting an empty string for years without sales and the numbers are left-aligned. The first problem can be solved with a litte more dax, but solving the second one looks like a terrible headache. The closest you could get would be by using a constant-width font (which may already be a no-go in many situations), count the maximum length including values and totals (which is not easy, especially because we have different formats) and then play with spaces in the front to make it look like it’s a number. Still everything will be left aligned because it’s still text, but you can fool yourself, but most likely nobody else will fall for it and will say that the matrix looks odd.
So enough with the measure, let’s look at the proper way to do it.
Going calc group on the problem
Calculation groups allow us to break this problem in two different parts, value and format. From the value side there’s nothing to do, so we’ll leave SELECTEDMEASURE. Now, for the format is where we’ll do some stuff, but even that will be easier than our previous attempt with the formatted measure.
------------------------------ -- Calculation Group: 'Format' ------------------------------ CALCULATIONGROUP 'Format'[Format] Precedence = 1 CALCULATIONITEM "Values without decimals" = SELECTEDMEASURE () FormatString = IF ( ISINSCOPE ( 'Product'[Manufacturer] ) && ISINSCOPE ( 'Date'[Year] ), "$ #,##0", "$ #,##0.00" )
In the format string expression the final value must be just the format string to appy to the value, in this case is the exact same value of the measure without any modification. And does it work?
nice isn’t it? we could wrap it up here, but as Alberto would say, «Can we do better?» Well, definately.
In our expression we are hardcoding the format strings, but it would be even better if we could just reuse the format string of the measure and just remove the decimals, isn’t it? In this case even if we used the calculation group with a percentage measure we could get the same effect.
You might be tempted to do something like removing the last 3 chars of the formatstring, but don’t be fooled by simple format strings, formatstrings can be quite complex.
Here we won’t solve the general problem, but we’ll assume that when a measure has decimals it contains the string «.00» in one or more places (in case it has a expression for positive, negative and 0). So we’ll just replace that with nothing and we’ll get the quivalent format string without decimals. Let’s see how that would work
------------------------------ -- Calculation Group: 'Format' ------------------------------ CALCULATIONGROUP 'Format'[Format] Precedence = 1 CALCULATIONITEM "Values without decimals" = SELECTEDMEASURE () FormatString = VAR originalFormatString = SELECTEDMEASUREFORMATSTRING () VAR fromatStringNoDecimals = SUBSTITUTE ( originalFormatString, ".00", "" ) VAR result = IF ( ISINSCOPE ( 'Product'[Manufacturer] ) && ISINSCOPE ( 'Date'[Year] ), fromatStringNoDecimals, originalFormatString ) RETURN result
We just store the original format string in a variable, we calculate the format string without decimals and then return one or the other depending on the evaluation context. Does it work?
Well it does work nicely!
And that’s it — simple but useful if that’s what you want to achieve! And much much better than anything you can do with just a measure.
You want the PBIX? here it is.
Follow the conversation in Twitter and LinkedIn
****
PS: something I didn’t mention is that the expression is dependant on the fields that are actually used in the matrix. Unfortunately there’s no way with today’s DAX to refer to the fields of the rows and column sections, so you’ll have to adapt it to each of the visuals in which you want to use them. Is not great, but hey, it is what it is.