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.
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.
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.
- PowerBI1016 years agoFrequent Visitor
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.
- ccolletti2 years agoHelper I
Cmcmahan this was really helpful for me in getting the value I wanted from my sublevel, but is there a way to calculate the value here and use at the next level up? Have a report I'm working on that I want to get the percent of compliance based off of the stores within a region
Some example data is below, where Compliance is the value I got from your measure above. I want to calculate those and divide by the total number of stores within it's region. I'd want to eventually get the following results: East - 67%, Central - 33%, West - 50%
Region Store Review Count Compliance East 1 20 1 2 5 0 3 7 1 Central 4 2 0 5 15 1 6 14 0 West 7 33 1 8 10 1 9 5 0 10 2 0 Thank you very much for your help!
- Water1 year agoHelper II
Excellent! Thank you, just what I was looking for. You saved me a couple of days!