I remember feeling that way, Mr. Badger, but they’re simple… if you can SEE them!
Have you ever needed to SUMX() over the values in more than one column? SUMMARIZE() can do it!
I tried solving this problem and the internet led me to SUMMARIZE(). It was stuffed inside another function and it confused me. I felt a lot like my friend in the above picture… discouraged. DAX had been so fluid until this point! There were learning curves and lots of new ideas, but I had never bumped into a function and heard my brain just say, “Nope.”
SUMMARIZE() was confusing me because I couldn’t see what was going on.
The problem was that with all of the others I had been exposed to, the functions were “follow-able.” Either the function intuitively followed its name or I was able to go into my data model and filter some things and SEE what was really happening (by the way, Power BI, get on that). With SUMMARIZE(), there were things happening and I couldn’t see under the hood.
Enter, Power BI Calculated Tables – Seeing SUMMARIZE() made easy!
Before Power BI calculated tables, the only way I knew of to see the results of a DAX table function was DAX Studio (made by our friends over at SQLBI). With this calculated table functionality in Power BI, seeing SUMMARIZE() is as easy as writing it. I’ve built a simple AdventureWorks data model with a Calendar, list of Products, and Sales.
Let’s start with a simple example: Summarizing one table…
We can go to Modeling>New Table in Power BI and try out SUMMARIZE() and see how it transforms a table:
Summarize_Test = SUMMARIZE(‘Calendar’,’Calendar'[CalendarYear],’Calendar'[CalendarQuarter])
SUMMARIZE() works by taking the table we gave it in the first argument (Calendar) and grouping the rows by the distinct combinations found in the columns we gave it in the second and third arguments (Calendar[CalendarYear] & Calendar[CalendarQuarter]). I like to think of it as “VALUES()-Plus.”
VALUES() takes 1 column & gives the unique values, SUMMARIZE() does the same for 2 or more.
To bring this full circle, let’s stuff our SUMMARIZE() into a formula and show how much SEEING the result helps us to understand what’s going on… Let’s say we want to know our average quarterly sales…
SalesTotal = SUM(Sales[SalesAmount])
AvgQtrlySales = AVERAGEX(SUMMARIZE(‘Calendar’,’Calendar'[CalendarYear],’Calendar'[CalendarQuarter]),[SalesTotal])
In the first measure, I simply write a measure to total up our sales dollars. In the second, we iterate over the combinations of ‘Calendar'[CalendarYear] and ‘Calendar'[CalendarQuarter] and treat each of them as if they were coordinates on a pivot table (filter context). This produces a SalesTotal for each Year/Quarter combo that we then take the average of.
Where does ADDCOLUMNS() fit into all of this?
Let’s say my explanation in the previous paragraph left you feeling a bit lackluster and you want to SEE the [SalesTotal] for each combination of Year and Quarter in a table. That’s where ADDCOLUMNS() comes in! Let’s take our Summarize_Test table from above, throw in an ADDCOLUMNS() and take a peek:
AddCol_Test =
ADDCOLUMNS(
SUMMARIZE(
‘Calendar’,
‘Calendar'[CalendarYear],
‘Calendar'[CalendarQuarter]
),
”Sales”,[SalesTotal]
)
ADDCOLUMNS() works very much the same as my explanation of [AvgQtrlySales] above, without any averaging; it took each combination of Year and Quarter as if they were coordinates on a pivot table (filter context) and it calculated [SalesTotal]. The first argument is the name of a table or the result of a table function (like SUMMARIZE()), the second is the name of the column we are about to add, and the third is the rule for creating it.
Tying it all together
A nagging question you may have had… “I can get these answers using a simple pivot table, why learn this?” The answer here is simple: Sure, we can get the values that ended up in our tables, but what if I want to use them dynamically in other calculations? Even if I use DAX to generate these tables, they will only refresh if I change the formula or refresh the data model. This provides a path for dynamic table calculation!
If we’re trying to hone in on what SUMMARIZE() and ADDCOLUMS() really do, SUMMARIZE() is the grouping guru and ADDCOLUMNS() is best at adding columns to DAX tables! The power that both of these functions provide far surpasses what we’ve talked about in our intro examples. Just like the first time I used CALCULATE(), we can use these patterns, without fully understanding them, to make it rain money for our companies. Play around with them and you’ll find all sorts of nuanced behavior, additional capabilities, and performance hacks.
#Badger
Come for the Techniques, Stay for the Business Value!
We get it: you probably arrived here via Google, and now that you’ve got what you needed, you’re leaving. And we’re TOTALLY cool with that – we love what we do more than enough to keep writing free content. And besides, what’s good for the community (and the soul) is good for the brand.
But before you leave, we DO want you to know that instead of struggling your way through a project on your own, you can hire us to accelerate you into the future. Like, 88 mph in a flux-capacitor-equipped DeLorean – THAT kind of acceleration. C’mon McFly. Where you’re going, you won’t need roads.
Get in touch with a P3 team member