ROLLUPGROUP

How to Use the Power BI DAX function ROLLUPGROUP

Understanding ROLLUPGROUP

The ROLLUPGROUP function in DAX allows users to aggregate data along multiple dimensions. It creates a hierarchical structure of subtotals and grand totals based on the specified grouping columns. This can be very useful when working with data that has multiple levels of granularity.

For example, suppose you have a sales table with columns for product, region, and month. You can use ROLLUPGROUP to create subtotals for each region, each product, and each combination of region and product. You can also create a grand total that includes all regions and all products.

Syntax

The syntax for the ROLLUPGROUP function is as follows:

ROLLUPGROUP(❰table❱, ❰column1❱, ❰column2❱, …)

- `❰table❱`: The name of the table to aggregate.

- `❰column1❱, ❰column2❱, ...`: The columns to group by.

Note that you can specify multiple columns to group by. The ROLLUPGROUP function will create subtotals and grand totals for each combination of values in the specified columns.

Implementation

Let's take a look at an example of how to use the ROLLUPGROUP function in Power BI. Suppose we have a sales table with columns for product, region, and month. We want to create subtotals for each region, each product, and each combination of region and product. We also want to create a grand total that includes all regions and all products.

Here's how we can do it:

1. Open Power BI Desktop and load the sales table into the data model.

2. Click on the "New Measure" button in the "Modeling" tab.

3. In the formula bar, enter the following formula:

Total Sales = SUM(sales[amount])

This creates a new measure that calculates the total sales for each row in the sales table.

4. Click on the "New Measure" button again and enter the following formula:

Rollup Sales = ROLLUPGROUP(sales, sales, sales[region])

This creates a new measure that aggregates the sales table by product and region. The ROLLUPGROUP function creates subtotals for each product, each region, and each combination of product and region. It also creates a grand total that includes all products and all regions.

5. Drag the "Rollup Sales" measure onto a table or chart visual to display the results.

In conclusion, the ROLLUPGROUP function in DAX is a powerful tool for aggregating data in a hierarchical way. It allows users to create subtotals and grand totals based on multiple dimensions, which can be very useful when working with data that has multiple levels of granularity. By following the steps outlined in this article, users can easily implement the ROLLUPGROUP function in Power BI and take their data analysis to the next level.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)