Forum Discussion
Only Total Select Values in a Matrix & Formatting Help
- 7 years ago
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-for-total-and-subtotals-in
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.
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
- Cmcmahan7 years agoResident 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!
- Cmcmahan7 years agoResident 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.- v-xicai7 years agoCommunity 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.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.