DAX in SQL Server Rollup Statement to Group and Total Data

By: Haroon Ashraf

Overview

The RollUp function is an add-on to the Summarize function, where Summarize aggregates data based on some grouping, Rollup gives sub-totals and a grand total. The RollUp function is used as an argument inside the Summarize function.  RollUp shows aggregated data (count, sum) of the grouped by columns first then over all aggregated data (count, sum) without any grouping and so on.

Getting Total Orders per Year without RollUp

In order to see the total number of orders per year we write the following DAX query:

EVALUATE(SUMMARIZE(Orders,DimDate[Year],"Total Orders",COUNT(Orders[OrderID])))

Getting Total Orders per Year and Overall Total Orders using RollUp

Total number of orders per year can be viewed without using RollUp, but if we want to view not only the total number of orders per year, but also total orders in all the years then we have to use RollUp as follows:

EVALUATE(SUMMARIZE(Orders,ROLLUP(DimDate[Year]),"Total Orders",COUNT(Orders[OrderID])))

Getting Total Orders per Customer per Month without RollUp

For example if we want to know the number of orders placed by a customer each month we can retrieve the data by using the following script without using RollUp:

EVALUATE(SUMMARIZE(Orders,DimDate[MonthName],Customer[Name],"Total Orders",COUNT(Orders[OrderId])))

Getting Total Orders per Customer per Month using RollUp

What if we are interested in retrieving data for the following things using a single DAX query:

1. Total Number of Orders per Customer per Month
2. Total Number of Orders per Month

This is where RollUp comes into action. The additional requirement of getting Total Number of Order per Monthis met by using RollUp as follows:

EVALUATE(SUMMARIZE(Orders,ROLLUP(DimDate[MonthName],Customer[Name]),"Total Orders",COUNT(Orders[OrderID])))

Summary

So we put RollUp inside Summarize and Summarize inside Evaluate, however, please remember as discussed before RollUp is only used within Summarize to give sub-totals and overall totals.  In other words, Evaluate takes Summarize as an argument and Summarizetakes RollUpas anargument and as long as we are getting an expression which can be a table (tabular rows) the DAX syntax is valid.