SUMMARIZE() & ADDCOLUMNS() aren’t scary… if you can SEE them!

badger 44210 640 thumb SUMMARIZE() & ADDCOLUMNS() aren’t scary… if you can SEE them!

I remember feeling that way, Mr. Badger, but they’re simple… if you can SEE them!

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

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.

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.

DataModel thumb SUMMARIZE() & ADDCOLUMNS() aren’t scary… if you can SEE them!

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 thumb SUMMARIZE() & ADDCOLUMNS() aren’t scary… if you can SEE them!

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

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.

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 1 thumb SUMMARIZE() & ADDCOLUMNS() aren’t scary… if you can SEE them!

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.

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

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.

Let’s block ads! (Why?)

PowerPivotPro