subtotal in matrix table

Anonymous — Thu, 01 Feb 2024 09:12:58 GMT

Hi,

is it possible in the visualisation with a matrix table to insert a subtotal under a specific row? For example

the matrix table shows that

but I need this

Thanks for any ideas

Re: subtotal in matrix table

123abc — Thu, 01 Feb 2024 09:53:45 GMT

In Power BI, you can use DAX (Data Analysis Expressions) to calculate subtotals in a matrix table. However, Power BI does not directly support inserting subtotals under specific rows in a matrix table like the Excel pivot table.

To achieve subtotaling under specific rows in a matrix table in Power BI, you can create calculated measures using DAX. Here's a general approach:

Define a measure for the subtotal: Create a DAX measure that calculates the subtotal based on the rows you want to subtotal.
Modify the matrix table: Add the subtotal measure to the matrix table and place it in the appropriate position.

Here's a basic example of how you can create a subtotal measure in DAX:

Subtotal Measure =
VAR SelectedRows = VALUES('YourTableName'[RowField]) // Identify the rows you want to subtotal
RETURN
CALCULATE(
[YourExistingMeasure], // Your existing measure to subtotal
FILTER(
ALL('YourTableName'[RowField]), // All rows from the row field
'YourTableName'[RowField] IN SelectedRows // Filter rows for subtotal
)
)

Replace 'YourTableName' with the name of your table and [RowField] with the column you are using to define rows in your matrix table. Also, replace [YourExistingMeasure] with the measure you want to subtotal.

For example, if you want to subtotal under the "Region" row in your matrix table, you can create a measure like this:

Region Subtotal =
VAR SelectedRegions = VALUES('YourTableName'[Region])
RETURN
CALCULATE(
[Total Sales], // Assuming 'Total Sales' is your existing measure
FILTER(
ALL('YourTableName'[Region]), // All regions
'YourTableName'[Region] IN SelectedRegions // Filter regions for subtotal
)
)

Then you can add this measure to your matrix table in Power BI and place it under the "Region" row.

This approach allows you to calculate subtotals under specific rows in a matrix table in Power BI using DAX measures.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: subtotal in matrix table

Anonymous — Thu, 01 Feb 2024 13:56:36 GMT

Hi, thanks for your help 😊

I have successfully created the subtotal according your example. But I can only display it in the matrix table under Values instead of Rows. I need the subtotal in Rows. There is no possibility, is there?

Re: subtotal in matrix table

Anonymous — Thu, 01 Feb 2024 14:09:50 GMT

I found the solution by chance. create the subtotal with New Group.

thanks for the impulse 😇

topic subtotal in matrix table in DAX Commands and Tips

subtotal in matrix table

Re: subtotal in matrix table

Re: subtotal in matrix table

Re: subtotal in matrix table