ROLLUPADDISSUBTOTAL

How to Use the Power BI DAX function ROLLUPADDISSUBTOTAL

Understanding ROLLUPADDISSUBTOTAL function

The ROLLUPADDISSUBTOTAL function is a DAX function that is used to aggregate data and add subtotals to a table. This function takes two or more arguments: the first argument is the expression that you want to aggregate, and the other arguments are the columns that you want to add subtotals to.

The syntax for the ROLLUPADDISSUBTOTAL function is as follows:

ROLLUPADDISSUBTOTAL(❰expression❱,❰column1❱,❰column2❱,…)

Here, the expression is the measure or column that you want to aggregate, and column1, column2, etc. are the columns that you want to add subtotals to.

Using ROLLUPADDISSUBTOTAL function in Power BI

To demonstrate the use of the ROLLUPADDISSUBTOTAL function in Power BI, let's take an example. Suppose we have a sales data table that contains the following columns:

- Date

- Product

- Sales

- Quantity

- Region

We want to create a Power BI report that shows the total sales and quantity for each product, region, and overall. We can use the ROLLUPADDISSUBTOTAL function to add subtotals to the table and calculate the overall total.

First, we need to create a new measure that calculates the total sales and quantity. We can use the SUMX function to calculate the total sales and quantity for each row in the table. The syntax for the SUMX function is as follows:

Total Sales = SUMX(‘Sales Data’,[Sales]*[Quantity])

Here, 'Sales Data' is the name of the sales data table, [Sales] is the name of the sales column, and [Quantity] is the name of the quantity column.

Next, we can use the ROLLUPADDISSUBTOTAL function to add subtotals to the table. We want to add subtotals for each product, region, and overall. The syntax for the ROLLUPADDISSUBTOTAL function is as follows:

Total Sales with Subtotals =

ROLLUPADDISSUBTOTAL([Total Sales],’Sales Data'[Product],’Sales Data'[Region])

Here, [Total Sales] is the measure that we created in the previous step, and 'Sales Data'[Product] and 'Sales Data'[Region] are the columns that we want to add subtotals to.

We can now create a table visual in our Power BI report and add the 'Sales Data'[Product], 'Sales Data'[Region], and [Total Sales with Subtotals] columns to the table. Power BI will automatically add subtotals for each product, region, and overall.

The ROLLUPADDISSUBTOTAL function is a powerful DAX function that can be used to add subtotals to a table and calculate the overall total. By using this function in your Power BI reports, you can create more meaningful and insightful visualizations that provide a better understanding of your data.

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)