How to add subtotal in table visual

In Power BI report, I have the table visual below, which includes invoice line item level data:

The user wants to view the data grouped by customer, with a subtotal row for each customer in the style shown below. The rows should dynamically change according to filters such as Invoice Date range and/or Item filter. Is this something that can be done in Power BI?

Thanks

Need Help

MasonMA
1 year ago
Hello j_w

Yes, this can be done through Matrix visual. When you set up your visual, drag your fields 'Customer', 'InvoiceNo.', 'Item', 'Price' and 'InvoiceDate' to 'Rows', your Measures 'Qty', and 'Amount' to Values.

When you 'Format your Visual' for Matrix, have your 'Row Subtotal' turned on but only leave 'Show Subtotal' On for 'Customer' and 'InvoiceNo.'. You can rename 'Subtotal label' for 'InvoiceNo.' to 'Subtotal'. as in below picture.

(For the Matrix layout and Style, i used 'Minimal' for Style and 'Tabular' for Layout. )

Then you can use your date table to filter your Matrix visual.

Hope this helps:)

6 Replies

MasonMA
Super User
1 year ago
Hello j_w

Yes, this can be done through Matrix visual. When you set up your visual, drag your fields 'Customer', 'InvoiceNo.', 'Item', 'Price' and 'InvoiceDate' to 'Rows', your Measures 'Qty', and 'Amount' to Values.

When you 'Format your Visual' for Matrix, have your 'Row Subtotal' turned on but only leave 'Show Subtotal' On for 'Customer' and 'InvoiceNo.'. You can rename 'Subtotal label' for 'InvoiceNo.' to 'Subtotal'. as in below picture.

(For the Matrix layout and Style, i used 'Minimal' for Style and 'Tabular' for Layout. )

Then you can use your date table to filter your Matrix visual.

Hope this helps:)
j_w
Helper IV
1 year ago
danextian thanks for the tips, it works! It's "repeat row headers".
j_w
Helper IV
1 year ago
Hi MasonMA , thanks for your post. I’m not sure if the sample data and columns in my question were too simplified, the actual table visual has many more columns. After following your suggested steps using a matrix, my matrix now looks like the one below, which is hard for the user to read, any suggestion please?

Also, the Subtotal label setting doesn’t seem to be working. I set it to "Subtotal", but the matrix still shows "Sub":
Thanks
- MasonMA
  Super User
  1 year ago
  Hello j_w
  
  In 'Row Subtotal' setting, you may need to Switch Off 'Show subtotal' for all items in 'Row level' dropdown except for 'Customer' and 'Report Group'.
  
  Same logic for Column Subtotal, you can adjust them here.
  
  Hope this works:)
j_w
Helper IV
1 year ago
Hi MasonMA thank you for your help. It’s close to what the user wants in terms of style. I now have the result shown below, is it possible to repeat the column values in the area I marked with a red line? I thought this might make it easier for some users to read the data in the same row.
- danextian
  Super User
  1 year ago
  Change the matrix layout to Tabular and look for "repeat category labels" or something.