cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cmcmahan
Resident Rockstar
Resident Rockstar

Only Total Select Values in a Matrix & Formatting Help

I'm trying to visualize travel expense data in a tabular format.  I wish to group this data first by Department (Unit Section in example), then by Unit (Unit Name in example), and then order within the group by locator.  I've tried using a table, but I can't group the records together.

 

I'm also trying to subtotal each Unit's amount spent and advance before moving on to the next unit.  I've gotten pretty close, but I'm continually running into the problem that a matrix wants/needs to summarize every value.  I've gotten around some of my issues by using a unique index as a 3rd grouping variable, which allows me to display each trip once I drill down.  

 

Here is what I am currently able to display, as well as my field options:

PowerBIRedacted.png

 

So my questions are as follows:

1. Is there a way to display each record horizontally instead of vertically?

2. If not, is there a way to set up multiple columns worth of data, putting each of these records next to each other in their appropriate groupings?

3. Is there some way to not show the Record Key value while still separating records by it?  It's not human readable and just adds noise to the data 

4. Is there a way to total only applicable values?  Showing the first alphabetical Locator and earliest departure date is useless info.  I only want the dollar total and advance totalled if possible.


If it's helpful, here is a redacted version of what I want the report to resemble (doesn't have to be exact):ReportRedacted.png


EDIT: Crossed out questions I figured out the answers to. Please find my solutions below.

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

So I've managed to figure out a solution to my problem, and I'm sharing in case anybody else wants to only show specific totals or subtotals in a matrix.

 

It took some digging, but I was able to figure out that the way Matrices calculate the totals/subtotals of a column is weird.  It doesn't just take all the values above and sum/average/find them.   It selects all the data, but filters at one level above that section's row groupings. 

For my example, I'll assume it's calculating the Passenger Name field.  My data is grouped by Unit Section -> Unit Name -> Record Key, so for each normal entry it would filter and find the first Name that matches the Record Key. There was only one, so it displayed correctly.  Then it would do the same for the subtotal, but this time it would get a list of all names in the Unit Section and Unit Name, selecting the 'first' one and calling that a total. 

 

The first thing I determined is that if I just put a raw text field into the Values section of a matrix, it would ALWAYS try to aggregate it in some way for the subtotal.  I was able to figure out that if you used a DAX measure, it could be aggregated differently than the basic aggregations, since it could evaluate arbitrary DAX code.  Knowing that it would have a list of values from different records, I found I could use the following to display nothing for most totals:

PASSENGER NAME = SELECTEDVALUE('Travel Data'[Traveler],BLANK())

 

This worked in MOST situations.  However, if all the values in a subsection were the same, or there was only one entry, it would still give me that value in the subtotal.  I took a few days away, and came back and found a way around this.

 

I found some information that you can use HASONEFILTER() to determine if there is a total being calculated instead of an individual row.  My problem is that at the subtotal level, there were still at least 2 filters on my data, so that didn't work.  It's obvious now, but I realized that each individual row was being filtered by Unit Section, Unit Name, and Record Key while each subtotal was only being filtered by Unit Section and Unit Name. I didn't see it sooner because it looked like individual rows were being treated as one row of data as I was using unique keys per entry, but under the hood they were technically aggregations of size one.

 

So finally, after days of fighting this, I was able to use this measure (one for each value of data) in order to only total the values I wanted:

PASSENGER NAME = IF(ISINSCOPE('Travel Data'[Record Key]),SELECTEDVALUE('Travel Data'[Traveler]),BLANK())

And we come to the end of the adventure.  PowerBI really needs to set up some sort of formatting option to not aggregate or total individual values in a matrix.  Supposedly the feature is being worked on, so I wholeheartedly suggest you vote for the idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...

 

Good luck out there!

 

EDIT: Updated my final query to reflect the better use of ISINSCOPE as opposed to ISFILTERED for the lowest level group.

View solution in original post

7 REPLIES 7
Cmcmahan
Resident Rockstar
Resident Rockstar

UPDATE:

I was able to find a workaround for #3.  I checked the column headers formatting, turned off word wrap, and shrank the column down to nothing.  It's still there, but not visible, which is an improvement

Cmcmahan
Resident Rockstar
Resident Rockstar

UPDATE:  I was able to get the records to display as rows.

 

To do this, I went into the formatting options for Values, and turned OFF "Show on rows".  

 

Now my only remaining problem is to not aggregate values like Name in the subtotals.  Any advice there would be very appreciated!

Cmcmahan
Resident Rockstar
Resident Rockstar

OK, last update for today:

I've managed to get everything I want done, except removing the aggregation in the subtotal lines.  This currently shows information like the first name in alphabetical order and earliest date from the group on the subtotal line, which is just noise.  

Supposedly if my matrix had columns, I could select which columns to total, but I've only got rows and values.  I've tried using ISINSCOPE() and conditional formatting to hide the text, I've tried changing the aggregation type, just about everything I can think of.   Is there any method or workaround to only total specific values?  Or to change the formatting on some totals but not others?

Below is what the current matrix looks like. I'm so close! If I can just remove the unhelpful totals, I'm there. Any guidance would be greatly appreciated. PowerBIRedactedUpdate.png

v-xicai
Community Support
Community Support

Hi @Cmcmahan ,

 

In the Matrix visual, go to Format->Subtotals, turn on per row level, then turn on total for "Active Category" field, and turn off total for "Active Sub Category" field. There is an example for your reference.

1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

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

 

Cmcmahan
Resident Rockstar
Resident Rockstar

That doesn't help me @v-xicai .  Setting the Per row level removes the entire row of subtotal information.  I WANT the subtotal for Amount, but NOT a "subtotal" for names, dates, etc.

Cmcmahan
Resident Rockstar
Resident Rockstar

So I've managed to figure out a solution to my problem, and I'm sharing in case anybody else wants to only show specific totals or subtotals in a matrix.

 

It took some digging, but I was able to figure out that the way Matrices calculate the totals/subtotals of a column is weird.  It doesn't just take all the values above and sum/average/find them.   It selects all the data, but filters at one level above that section's row groupings. 

For my example, I'll assume it's calculating the Passenger Name field.  My data is grouped by Unit Section -> Unit Name -> Record Key, so for each normal entry it would filter and find the first Name that matches the Record Key. There was only one, so it displayed correctly.  Then it would do the same for the subtotal, but this time it would get a list of all names in the Unit Section and Unit Name, selecting the 'first' one and calling that a total. 

 

The first thing I determined is that if I just put a raw text field into the Values section of a matrix, it would ALWAYS try to aggregate it in some way for the subtotal.  I was able to figure out that if you used a DAX measure, it could be aggregated differently than the basic aggregations, since it could evaluate arbitrary DAX code.  Knowing that it would have a list of values from different records, I found I could use the following to display nothing for most totals:

PASSENGER NAME = SELECTEDVALUE('Travel Data'[Traveler],BLANK())

 

This worked in MOST situations.  However, if all the values in a subsection were the same, or there was only one entry, it would still give me that value in the subtotal.  I took a few days away, and came back and found a way around this.

 

I found some information that you can use HASONEFILTER() to determine if there is a total being calculated instead of an individual row.  My problem is that at the subtotal level, there were still at least 2 filters on my data, so that didn't work.  It's obvious now, but I realized that each individual row was being filtered by Unit Section, Unit Name, and Record Key while each subtotal was only being filtered by Unit Section and Unit Name. I didn't see it sooner because it looked like individual rows were being treated as one row of data as I was using unique keys per entry, but under the hood they were technically aggregations of size one.

 

So finally, after days of fighting this, I was able to use this measure (one for each value of data) in order to only total the values I wanted:

PASSENGER NAME = IF(ISINSCOPE('Travel Data'[Record Key]),SELECTEDVALUE('Travel Data'[Traveler]),BLANK())

And we come to the end of the adventure.  PowerBI really needs to set up some sort of formatting option to not aggregate or total individual values in a matrix.  Supposedly the feature is being worked on, so I wholeheartedly suggest you vote for the idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...

 

Good luck out there!

 

EDIT: Updated my final query to reflect the better use of ISINSCOPE as opposed to ISFILTERED for the lowest level group.

Thank you for this tip, it is extremely helpful in making matrix visualizations.

 

I'd like to try my luck and ask you, do you know if it possible to modify this measure to react to expanding down / drilling up on a matrix visualization? I'm trying to display hour allocations and account numbers for projects and I have four row levels that I'm using:

Project

Year

Role

Employee name

 

With two values:

Hours (numeric)

Account number (text)

 

I'm usually displaying the matrix values on role level, which means that if the end user want to see the employee names of the roles, they can expand down the matrix visualization to the lowest level. For the measure, I'm using ISINSCOPE('Table'[Role Name]). This works very well on the role level that I am displaying. However, I would like to also disable subtotal for the text field if the end user expands down to employee name level. The current measure also works there to display the wanted text (account numbers), but Power BI shows a subtotal for the text column.

 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors