Group row subtotals based on a column in Matrix visualization

[jasonyeung87]

Hi,

I currently have a Matrix in which I display the row subtotals. It currently looks like:

The subtotal is the total of all the numbers in red. The rows could belong to either user name D or H. I want the subtotals to be grouped by the username, so it could show multiple rows, based on this column. Using the above example, it will have 2 rows and show the total of each user name (e.g. 18 hours for D and 1 for H).

I was wondering if someone has done something similar before? Any help is appreciated. Thanks!

I tried to look at the settings of the row subtotals, but I couldn't find where it could be set. My current settings for row subtotals are:

The fields in my matrix are:

Sincerely,

Jason

[v-hashadapu · Accepted solution]

Hi @jasonyeung87 , Thank you for reaching out to the Microsoft Community Forum.

It looks like Username isn’t placed in the “Rows” area or if it is, it may not be at the right level in the hierarchy to calculate subtotals correctly. To fix this, you’ll need to add Username to the Rows section of your matrix visual and make sure it’s placed above “Work Item Title” or any other row-level fields. Power BI calculates subtotals based on this order, so “Username” must come first if you want it to group the data and subtotal by user. Once that’s done, the matrix will automatically organize the items under each username, giving you a subtotal for each user.

Then expand the individual row levels and make sure “Show subtotal” is turned on for Username and optionally turned off for “Work Item Title” or other nested fields. This will ensure that you only get one subtotal per user, rather than multiple subtotal lines that might clutter the visual. Lastly, just double-check that Hours is still in the Values area and is set to sum.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[jasonyeung87]

Hi,

Thanks for your advice! I dragged the UserName to the rows (experimented on which order to put it in and it worked).

The one thing I was stuck on is that the "subtotals" of the other values appear. They're the date and comments field. As they're not numbers, it's displaying the first value of each grouping. Ideally I can make these not appear.

Jason 

[ryan_mayu]

@jasonyeung87 

if you want to subtotal username, then I think you have to drag your username column to row field.