ROLLUPISSUBTOTAL

How to Use the Power BI DAX function ROLLUPISSUBTOTAL

In this article, we will explore what the ROLLUPISSUBTOTAL function is, how it works, and how you can use it to create subtotals and grand totals in your Power BI reports.

What is the ROLLUPISSUBTOTAL Function?

The ROLLUPISSUBTOTAL function is a DAX function that calculates subtotals and grand totals in a table. This function takes two arguments:

1. The expression that you want to calculate.

2. The column that you want to use to calculate the subtotals and grand totals.

The ROLLUPISSUBTOTAL function works by calculating the expression for each row in the table, and then aggregating the results based on the values in the column that you specified.

How Does ROLLUPISSUBTOTAL Work?

To understand how the ROLLUPISSUBTOTAL function works, consider the following example:

Suppose you have a sales table that contains the following columns: Product, Region, Quarter, and Sales. You want to calculate the total sales for each region, as well as the grand total for all regions.

To do this, you can use the ROLLUPISSUBTOTAL function as follows:


CALCULATE(

SUM(Sales[Sales]),

ROLLUPISSUBTOTAL(Sales[Sales], Sales[Region])

)


This formula calculates the sum of the Sales column, and then uses the ROLLUPISSUBTOTAL function to aggregate the results based on the values in the Region column.

The ROLLUPISSUBTOTAL function works by first calculating the expression (SUM(Sales[Sales])) for each row in the table. It then aggregates the results based on the values in the Region column, creating subtotals for each region. Finally, it calculates the grand total by summing the subtotals.

Using ROLLUPISSUBTOTAL in Power BI

To use the ROLLUPISSUBTOTAL function in Power BI, follow these steps:

1. Open Power BI Desktop and create a new report.

2. Import your data into Power BI.

3. Create a new measure (or edit an existing one) and enter the ROLLUPISSUBTOTAL formula.

4. Add the measure to your report, and use it to create subtotals and grand totals.

When using the ROLLUPISSUBTOTAL function in Power BI, it is important to select the correct column to use for the subtotals and grand totals. You should also ensure that your data is properly formatted, and that there are no missing values or errors.

The ROLLUPISSUBTOTAL function is a powerful tool that can be used to calculate subtotals and grand totals in a table. This function is especially useful when working with large data sets that require complex calculations.

To use the ROLLUPISSUBTOTAL function in Power BI, simply create a new measure (or edit an existing one) and enter the formula. You can then use the measure to create subtotals and grand totals in your reports.

Remember to select the correct column to use for the subtotals and grand totals, and to ensure that your data is properly formatted. With these best practices in mind, you can use the ROLLUPISSUBTOTAL function to create powerful reports in Power BI.

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

Upcoming Courses

6-8 Jun 23 (London or Online)
8-10 Aug 23 (London or Online)
24-26 Oct 23 (London or Online)

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)