Forum Discussion
How to disable sub totals for string values on matrix visual?
I have a report I am developing using the matrix visual that has two columns from my dataset in the rows column, RowColumnOne and RowColumnTwo, which are both strings. I also have several columns from the dataset in the values column, which can be strings or numbers. This is so that I can get a hierarchical display of the two columns used as rows.
In the subtotal columns for each value column, it is correctly summing the numeric values. However for the string values, it is taking the first instance of each string it sees and is putting it as the sub total. I would like to disable the string fields in the sub total while keeping the numeric ones. What would be the best way to do this?
Current:
| Group 1 | Numeric | String |
| Sub-Group 1 | 2 | Hello |
| Sub-Group 2 | 7 | World |
| Total | 9 | Hello |
Desired:
| Group 1 | Numeric | String |
| Sub-Group 1 | 2 | Hello |
| Sub-Group 2 | 7 | World |
| Total | 9 |
Zak-Echelon is ISINSCOPE dax function to achieve this. There is a great post on it.
5 Replies
- parry2k
Super User
Zak-Echelon is ISINSCOPE dax function to achieve this. There is a great post on it.
- jdbuchanan71
Hello Zak-Echelon
You would need to write a measure to check the level of the row and only display for the [sub-groups]. Something like this:
String Display = IF ( ISINSCOPE ( 'YourTable'[Sub-Group] ), MIN ( 'YourTable'[String] ) )This will show blank on the total row for the string field.
Currently, my ISINSCOPE function returns false for every row of the dataset. Is there a specific way I need to configure my data to get it to work properly?
Here is a screenshot of the matrix configuration panel from power bi desktop. Cannot share a picture of the matrix itself as it has confidential data. The is inscope measure is just IsInScopeMeasure = ISINSCOPE(Table[Column])
- parry2k
Zak-Echelon did you had chance to go thru the post I sent to you earlier, you will wrap your existing measure in If or switch condition so that it doesn't show the text on total.
