The result is a list of the distinct SalesOrderNumbers we need (just like VALUES produced). In this formula, the inner SUMMARIZE is working over the FactInternetSales table and grouping by SalesOrderNumber. Anyway, probably easier with an example, so here's our formula: Those last two optional parts give SUMMARIZE an extra bit of flexibility that can come in handy when you need more than just a single-column list back. And you can add aggregation expressions, similar to a GROUP BY in SQL. Optionally, you can group by additional columns. The second parameter is the column to group by (FactInternetSales) The first parameter is the table you want to roll up (FactInternetSales) But instead of using VALUES to get the list of SalesOrderNumbers, we'll use SUMMARIZE to do the grouping. And then it calculates the average of those values for the final result.Įssentially, this approach accomplishes the same thing as approach #2 above. For each 'ticket', it executes the CALCULATE(SUM) to roll SalesAmount up to the ticket level. The AVERAGEX is the outer function that iterates over each of the items in the list (each SalesOrderNumber or 'ticket'). The CALCULATE(SUM()) defines how we will roll SalesAmount up to the ticket level (we're grouping by SalesOrderNumber here).ģ. The VALUES function behaves like a subquery and returns a list of the distinct SalesOrderNumbers that we want to group by (the list of tickets).Ģ. There are three key parts to this formula.ġ. Here's the actual formula for our scenario and we'll break into it's components. We group by the 'ticket' and sum up the underlying item sales amounts, and then calculate an average across all of the tickets. With the next two approaches, instead of rolling all the way up like in #1, we want to take a two-step approach. Regardless, here's the formula for reference. Anyway, it works in this scenario, but you won't always be able to calculate in 1 pass like this.

I'm just including it to be thorough in solving the scenario at hand. This is probably the simplest approach and actually doesn't do any grouping. There are actually several ways to accomplish this, so let's walk through them one at a time.Īpproach #1: Total Sum of Sales / Distinct Tickets This is a very common requirement, and one that is easily satisfied in DAX.

Our challenge then is to roll up sales to the ticket level (sum it up by ticket), and then average across tickets. But we want to analyze at the ticket level, not the item level. And we want to analyze average sales over time or sliced by segment. We've got sales data at the line item level (i.e., the individual items purchased within a given shopping basket or ticket). Let's take Internet Sales (the FactInternetSales table in AdventureWorksDW). We'll use AdventureWorks since everybody knows that dataset. Ok, so let's work through this with a demo scenario.

So here's my spin on it from a PowerPivot expression perspective. The other day I helped someone in the forums with a grouping question like this, and he asked for some resources to learn more about this useful technique. Luckily DAX has a few Groupers that can handle this quite nicely. Basically, the need to achieve the equivalent of SQL's GROUP BY in DAX, and then apply some additional calcs. Rolling up data to a higher level of granularity and then performing additional calculations on it. Sometimes you need to perform what I'd call a multi-pass aggregation.

Summarize dax