Hello!
Today (well yesterday by the time I have finished writing this) saw a great use case of calculation groups and I wished I had come up with the idea myself because it’s awesome and something that I’ve come across sometimes. In a table there’s a breakdown by month, and at the total they want to see sum, but also want to add another column with say the average, but could be also the value last year or growth. Yes, I’m talking about the latest video from Chandeep Chabbra. The video is beautifully set up so it’s definitively worth a watch.
Even though there’s a few things I would change from the DAX of the format string expressions, that alone would not justify a blog post about the same use case. But yet I wanted to play with the calc group, so what I plan to do instead is to show you the process to «industrialize» this calculation group, i.e. how to create a script that will replicate similar logic whenever you want to use it. Yes, I mean a c# script. That’s something I’ve been doing lately in some sessions, but it’s not yet in the blog so why not use this occasion for it.
Build your Calc group manually first
Well, since we are going to industrialize it, let’s first make sure we like it. I rewatched the video to see all the different details and tried to build it myself. I like to keep logic into nice compartments, so instead of doing all the time intelligence on the spot, I created a couple of calculation groups. One has the time intelligence calculations, and the other one just for statistics. In this case it calculates de Average by month, a regular «ask» as they call it now. People is too lazy to say «request» these days?
So I had now 3 calc groups in place that looked like this:
The «Totals» calc group that is a simplified verssion of the calculation group of the video
------------------------------ -- Calculation Group: 'Totals' ------------------------------ CALCULATIONGROUP 'Totals'[Totals] Precedence = 1 CALCULATIONITEM "Months" = SELECTEDMEASURE () Ordinal = 0 CALCULATIONITEM "PY" = IF ( NOT ISINSCOPE ( 'Date'[Month] ), CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Time Intelligence] = "PY" ) ) Ordinal = 1 CALCULATIONITEM "AVG" = IF ( NOT ISINSCOPE ( 'Date'[Month] ), CALCULATE ( SELECTEDMEASURE (), 'Statistics'[Statistics] = "Avg by Month" ) ) Ordinal = 2
As you see I moved all the logic of the «PY» and «AVG» calc items to two different calc groups. For reference the calc items in these groups look like this (simplified code)
CALCULATIONGROUP 'Statistics'[Statistics] Precedence = 2 CALCULATIONITEM "Avg by Month" = CALCULATE ( VAR months = VALUES ( 'Date'[Year Month] ) VAR result = AVERAGEX ( months, SELECTEDMEASURE () ) RETURN result, ALLSELECTED ( 'Date' ) ) CALCULATIONGROUP 'Time Intelligence'[Time Intelligence] CALCULATIONITEM "PY" = IF ( [ShowValueForDates], CALCULATE ( SELECTEDMEASURE (), CALCULATETABLE ( DATEADD ( 'Date'[Date], -1, YEAR ), 'Date'[DateWithSales] = TRUE ) ) )
Actually things looked quite nice once you put the fields in the right place and do the drill down on columns
Yet there was something I didn’t like. The «Quarter» calc item was on the left and I wanted it on top of the Total, like PY and AVG. Actually it would be awesome if we could put those three names instead of «Total, Total, Total» but unfortunately those three go together. One possibility is to leave it blank. But hey, first things first, how do I bring «Quarter» to the right without aligning months to the right too? Well, I thought about using the same trick that we use for PY and AVG, play around with the «ISINSCOPE» function. And gave it a go. The new version of the Totals calc group looked like this, separating the «Quarter» calc item into two, «Months» to show the months, «Quarter» to show the total :
CALCULATIONGROUP 'Totals'[Totals] Precedence = 1 CALCULATIONITEM "Months" = IF ( ISINSCOPE ( 'Date'[Month] ), SELECTEDMEASURE () ) Ordinal = 0 CALCULATIONITEM "Quarter" = IF ( NOT ISINSCOPE ( 'Date'[Month] ), SELECTEDMEASURE () ) Ordinal = 1 CALCULATIONITEM "PY" = IF ( NOT ISINSCOPE ( 'Date'[Month] ), CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Time Intelligence] = "PY" ) ) Ordinal = 2 CALCULATIONITEM "AVG" = IF ( NOT ISINSCOPE ( 'Date'[Month] ), CALCULATE ( SELECTEDMEASURE (), 'Statistics'[Statistics] = "Avg by Month" ) ) Ordinal = 3
Dit it work?
So close! But actually is not so bad. Changing the total label to one space and making that column very narrow, the result is quite good indeed.
Thinking time
Ok, now that we have the calc group we want, we need to think how this ‘industrialization’ should go.
First we should pick a field that will be used for the columns of the matrix since this one appears in the dax expressions of all calc items of the Totals calc group. So the first step could be just right clicking on a column and say, «build me a Totals calc group based on this column». But then how should the rest go?
Should we select a calc item of a different calc group and say «please add a total based on this calc item» and then point to the «totals» calc group? In theory we could have more than one in the same model, one based on months and another one on days of the week, or even an entire different table. Hmmm…. So many questions. We could also go the other way around, right click on the totals calculation group and then with a dialog window ask the user to select the calc group and calc item that should be added as a new total. If we live in a TE3-only world, we could even expect the user to select them both simultaneously before running the macro, but unfortunately TE2 does not allow selection of objects from different tables. Who knows if this will be fixed one day, but for now, it is what it is.
Getting practical the first approach (right-clicking on calc item and selecting later the calc group where the new total should be added) is better. The reason is that there is not a native «SelectCalcItem» function in Tabular Editor. We can probably hack the SelectObject to do it, but even in that case we’ll be adding more user interactions in our macro. When building the initial calc group we can leave there an annotation saying «this is a total calc group» and then when we right click on the calc item, as long as there is only one total calc group in the model, we have no need to ask anything to the user. Just pick the only total calc group with that annotation and continue!
Coding time
Ok, so we have a plan. Time to start coding our way. We are going to use the setup that I explained in this article.
This first snippet is basic housekeeping. We need to make sure that the user is right clicking on one column and one column only.
if(Selected.Columns.Count() != 1)
{
Error("Select only 1 column and try again");
return;
}
This being out of the way, let’s get something working and then we can sophisticate it as much as we want or we have time to. First let’s just build the bare minimum to replicate what we had:
string calcGroupName = "Totals";
CalculationGroupTable calcGroup = Model.AddCalculationGroup(calcGroupName);
string valuesCalcItemName = "Values";
string valuesCalcItemExpression =
@"IF(
ISINSCOPE( 'Date'[Month] ),
SELECTEDMEASURE()
)";
CalculationItem calcItem =
calcGroup.AddCalculationItem(
name: valuesCalcItemName,
expression: valuesCalcItemExpression);
Here we did a few things already. Notice that even if it now looks overkill, we use variables when we call functions instead of hardcoding the values directly. This will help us later when we want to make it more sophisticated. Also, notice the «@» before the valuesCalcItemExpression value. If we start a string with @»» and then paste whatever string we want between those two double quotes, it will take care of doubling the double quotes and will accept line breaks and some other nice things.
However this code still does not fulfill the bare minimum because the expression is unrelated to the column that we have selected. Let’s fix that with String.Format
Column column = Selected.Column;
string valuesCalcItemExpression =
String.Format(
@"IF(
ISINSCOPE( {0} ),
SELECTEDMEASURE()
)",column.DaxObjectFullName);
I added a column variable storing the selected column because we’ll be using that a lot. We can now continue and create the other calc item too. We’ll show here how can we format the dax and add a description of the calculation item (not visible today in Power BI Desktop, but it never hurts do document the objects).
string totalCalcItemName = "Total";
string totalCalcItemExpression =
String.Format(
@"IF(
NOT ISINSCOPE( {0} ),
SELECTEDMEASURE()
)",column.DaxObjectFullName);
CalculationItem totalCalcItem =
calcGroup.AddCalculationItem(
name:totalCalcItemName,
expression:totalCalcItemExpression);
totalCalcItem.FormatDax();
totalCalcItem.Description = "This calculation item is to show the regular total as a calculation item along with different totals that will be added to this calculation group";
Beautiful! But now we need to think a little more. We’ll have now to create another script executed on a calculation item (we’ll in this case could be more than a single calculation item), but we’ll have to find the Totals calculation group, and not only that, we’ll have to find the column we selected when we created it to create the new calculation items. How are we going to do that?
The answer is: «Annotations»
Annotations allow us to store pairs of strings in our model inside almost any object you can think of. You might need to enable unsupported actions to see and modify them, but I have not run into problems so far playing with them (no warranties though!). So we’ll add two annotations at the calculation group level, one to distinguish the calc group «type» and one to store the slicing column.
string calcGroupTypeLabel = "CalcGroupType";
string calcGroupTypeValue = "MultiTotal";
calcGroup.SetAnnotation(
calcGroupTypeLabel,
calcGroupTypeValue);
string calcGroupValuesFieldLabel = "ValuesField";
string calcGroupValuesFieldValue = column.DaxObjectFullName;
calcGroup.SetAnnotation(
calcGroupValuesFieldLabel,
calcGroupValuesFieldValue);
Let’s complete this script and make the name configurable at run-time but with a nice suggestion already there. The name should be based on the column name, something like {Column Name} Multi-Totals. In order to be able to ask the user at run-time we’ll need to add the Microsft.VisualBasic library and use the Interaction.Messagebox function. Remember we need to add the commented #r and using commands in our stript in Visual Studio to get it nice and ready when we copy that to Tabular Editor with the «Get Macro» macro of course.
Inside visual studio things look like this now:
namespace TE_Scripting
{
public class TE_Scripts
{
void createNewTotalsCalcGroup()
{
//#r "Microsoft.VisualBasic"
//using Microsoft.VisualBasic;
if (Selected.Columns.Count() != 1)
{
Error("Select only 1 column and try again");
return;
}
Column column = Selected.Column;
string suggestedCalcGroupName = column.Name + " Multi-Totals";
string calcGroupName = Interaction.InputBox(
Prompt:"Please provide the name of the multi-total calc group.",
DefaultResponse:suggestedCalcGroupName);
if (calcGroupName == "")
{
Error("No name provided");
return;
};
CalculationGroupTable calcGroup =
Model.AddCalculationGroup(
calcGroupName);
string valuesCalcItemName = "Values";
string valuesCalcItemExpression =
String.Format(
@"IF(
ISINSCOPE( {0} ),
SELECTEDMEASURE()
)", column.DaxObjectFullName);
CalculationItem valuesCalcItem =
calcGroup.AddCalculationItem(
name: valuesCalcItemName,
expression: valuesCalcItemExpression);
valuesCalcItem.FormatDax();
valuesCalcItem.Description = "This calculation item is to show the breakdown by " + column.Name;
string totalCalcItemName = "Total";
string totalCalcItemExpression =
String.Format(
@"IF(
NOT ISINSCOPE( {0} ),
SELECTEDMEASURE()
)", column.DaxObjectFullName);
CalculationItem totalCalcItem =
calcGroup.AddCalculationItem(
name: totalCalcItemName,
expression: totalCalcItemExpression);
totalCalcItem.FormatDax();
totalCalcItem.Description = "This calculation item is to show the regular total as a calculation item along with different totals that will be added to this calculation group";
string calcGroupTypeLabel = "CalcGroupType";
string calcGroupTypeValue = "MultiTotal";
calcGroup.SetAnnotation(
calcGroupTypeLabel,
calcGroupTypeValue);
string calcGroupValuesFieldLabel = "ValuesField";
string calcGroupValuesFieldValue = column.DaxObjectFullName;
calcGroup.SetAnnotation(
calcGroupValuesFieldLabel,
calcGroupValuesFieldValue);
}
//plenty of other stuff
}
}
It’s time to test this one (well probably we should test before getting to this point too). Remember to save your file in Visual Studio before going to Tabular Editor and use «Get Macro».
Indeed the annotations are there:
And if the expressions are beautiful too. (I saved the changes back to the model and used TE3 DAX scripts to get this)
------------------------------------------ -- Calculation Group: 'Month Multi-Totals' ------------------------------------------ CALCULATIONGROUP 'Month Multi-Totals'[Name] Precedence = 3 CALCULATIONITEM "Total" = IF ( NOT ISINSCOPE ( 'Date'[Month] ), SELECTEDMEASURE () ) Description = "This calculation item is to show the regular total as a calculation item along with different totals that will be added to this calculation group" CALCULATIONITEM "Values" = IF ( ISINSCOPE ( 'Date'[Month] ), SELECTEDMEASURE () ) Description = "This calculation item is to show the breakdown by Month"
Now that I see the expression I realize that the order of the calc items has not been set and it’s incorrect. Oh well. We’ll just add a couple of lines right after creating each calc item or at the end of the script
valuesCalcItem.Ordinal = 0; totalCalcItem.Ordinal = 1;
It’s time now to create the second script, the one that will add new totals for each of the selected calc items.
Here housekeeping is a bit trickier. We need to make sure that this macro is run after the one we just did. So, is there any calc group with that CalcGroupType annotation? We could formulate an expression that returns true or false to that using the LINQ expression «Any» but in case we find some we want to do things with it, so I’ll store all the ones I find in a «IEnumerable<Table>» variable (like a list, but not quite — I don’t understand c# either). Yes I’m using Table instead of CalculationGroupTable because for now that’s enough and makes the syntax easier. When we want to use it as a calculation group we can cast it.
string calcGroupTypeLabel = "CalcGroupType";
string calcGroupTypeValue = "MultiTotal";
IEnumerable<Table> multiTotalCalcGroups =
Model.Tables.Where(
t =>
t.GetAnnotation(calcGroupTypeLabel)
== calcGroupTypeValue);
Now, depending on how many are found we’ll do 3 different things. If none is found we’ll tell the user to run the other macro first. If one is found we’ll just store it into a variable and continue our way. But if we find more than one, we’ll need to ask the user which one should we use this time around.
Table calcGroupAsTable = null as Table;
if(multiTotalCalcGroups.Count() == 0)
{
Error("No multi-total calc group found. " +
"Run the macro to create a multi-total " +
"calc group first and try again");
return;
} else if(multiTotalCalcGroups.Count() == 1)
{
calcGroupAsTable = multiTotalCalcGroups.First();
}
else
{
calcGroupAsTable = SelectTable(multiTotalCalcGroups, label: "Select Multi-total Calc Group to use");
if(calcGroupAsTable == null)
{
Error("You cancelled the execution.");
return;
}
}
I stored the first script we did as a macro to run on columns, and created a second Multi-total Calc group on Year. Then I run the script we have written so far, and indeed, it shows the user the two Multi-total Calc groups and asks to choose one.
If the code continues it means that we have the multi-total calc group stored in our calcGroupAsTable variable. Great. But we also need to check that at least one calculation item is selected when the execution starts. This one is easier.
if(Selected.CalculationItems.Count() == 0)
{
Error("Select one or more calculation items and try again.");
return;
}
Actually you may choose to check this one first, your call. Just make sure you check everything you can from the model before you start asking the user to input names an things that if you have to repeat causes frustration and eventually anger.
Now it’s time to retrieve the code to refer to the column and start iterating over the calculation items.
string calcGroupValuesFieldLabel = "ValuesField";
string multiTotalBreakDownColumnCode =
calcGroupAsTable.GetAnnotation(calcGroupValuesFieldLabel);
CalculationGroupTable calcGroup = calcGroupAsTable as CalculationGroupTable;
foreach(CalculationItem calcItem in Selected.CalculationItems)
{
string calcItemName = calcItem.Name;
string calcItemExpression =
String.Format(
@"IF(
NOT ISINSCOPE( {0} ),
CALCULATE(
SELECTEDMEASURE( ),
{1} = ""{2}""
)
)",
multiTotalBreakDownColumnCode,
calcGroupAsTable.Columns[0].DaxObjectFullName,
calcItem.Name);
CalculationItem customTotalCalcItem =
calcGroup.AddCalculationItem(
name:calcItemName,
expression:calcItemExpression);
customTotalCalcItem.FormatDax();
}
Using the @»» before pasting the code between the two double quotes was providential in getting it right that you need to double the double quotes inside the string. When you paste it in Visual studio it goes to the left starting on the second row because of course you don’t have all that many tabs in your original expression, but it’s ok to add the tabs to make the code look there, because later we’ll format it again using DaxFormatter, so no harm done. Here is also worth mentioning the use of calcGroupAsTable.Columns[0].DaxObjectFull name. That’s a way to refer to the first column of the calc group table, which is the one that you need to filter to apply a calculation item. Also notice that we label the place holders as {0},{1},{2}… and we need to provide the arguments in the same order afterwards. But that’s about it!.
Test and refine
Time now to see if the whole thing works or further adjustments are needed. I stored the second script as a macro for calculation item, like this:
So I run it on the «PY» calc item of the time intelligence calc group and run it aaaand…
Auch, wrong calc group!
Indeed, I used the variable of the multi-total calc group instead of the calc group of the selected calc Item. It’s ok, errors happen.
We just need to do a small change to our script:
//calcGroupAsTable.Columns[0].DaxObjectFullName, calcItem.CalculationGroupTable.Columns[0].DaxObjectFullName
It’s nice that based on any object you can get the parent objects as well. Once the script is fix, we update the macro with the new code (very important!) and try again. In TE2 you can update a macro by selecting it from the «code snippets menu» and saving it again with the original name.
Oh yes!
However I realized I was adding calc items to a calc group that did not have the ordinal value set up for «Values» and «total» as it was created with an earlier version of the script. So I deleted it and created it with the latest version and then added a few calc items with the second script. So smooth!
But let’s check if all is good when we save it back to Power BI Desktop
The format string! When you think you have it, the format string always comes and stabs you on your back.
oh well, I guess I had it coming. After all, when we apply calculation groups by DAX, we loose the dynamic format string capabilities of the calculation group. Right? Well, not quite! A trick I learned from Owen Auger is that if you replicate the value expression in the format string expression, but you replace SELECTEDMEASURE() by SELECTEDMEASUREFORMATSTRING() you get pretty much what you are looking for. We’ll add then the formatstring expression to the calculation items we add.
string calcItemFormatStringExpression =
String.Format(
@"IF(
NOT ISINSCOPE( {0} ),
CALCULATE(
SELECTEDMEASUREFORMATSTRING( ),
{1} = ""{2}""
)
)",
multiTotalBreakDownColumnCode,
calcItem.CalculationGroupTable.Columns[0].DaxObjectFullName,
calcItem.Name);
customTotalCalcItem.FormatStringExpression =
calcItemFormatStringExpression;
Alright! Time to test the script with the new code. I delete the calc items and recreate them with the updated (don’t forget!) macro. Save back to Power BI Desktop aaaand…
YES!
this time these works beautifully! I can hear Claudio Trombini (the ultimate visualization wizard) saying «Bernat, YOY% is a change so you should have a + sign in front of positive values». Andhe is right! But that’s something to fix on my Time Intel calc group script… As Power BI PMs say, «We have it in our backlog». Somewhere.
Anyway, the purpose of this article was to show the process of creating a script to automate a calc group, but as you can imagine you can also create measures or even calculated tables if you want. But maybe you are now saying, «I’m not going to start csharping today, but I want that script, can I have it?» Of course you can!
- Here’s the base script to create the multi-total calc group
- And here’s the script to add custom totals to your matrix
In case you don’t believe the screenshots, you can download the pbix and check it out
And if you want to see how smooth it is to work with c# macros, check it out!