Forum Discussion
Helper VHow to exclude or hide a specific row subtotal in a Table Matrix visualization?
Hi All,
Can someone please suggest me how can I hide or exclude the subtotal of the row highlighted in red below from my table matrix in powerbi?
I just want to show %Occupied[marked in yellow] and remove the Total occupied row as [marked in red] as below expected output.
Is there a way to achieve it? or use RLS to hide the Total Occupied row from table matrix for all users? or any possible DAX. Please suggest.
Expected Output: I dont want to show the row marked in red and only i want to show the row marked in yellow in the subtotal in table matrix visual.
My input data is as below :-
| Date | TotalSeats | Occupied Seats | School Name | Buildings |
| 21-03-2021 | 10 | 1 | School1 | Building1 |
| 22-03-2021 | 10 | 4 | School1 | Building1 |
| 23-03-2021 | 10 | 4 | School1 | Building1 |
| 24-03-2021 | 10 | 1 | School1 | Building1 |
| 25-03-2021 | 10 | 2 | School1 | Building1 |
| 26-03-2021 | 10 | 1 | School1 | Building1 |
| 27-03-2021 | 10 | 1 | School1 | Building1 |
| 21-03-2021 | 12 | 5 | School1 | Building2 |
| 22-03-2021 | 12 | 8 | School1 | Building2 |
| 23-03-2021 | 12 | 8 | School1 | Building2 |
| 24-03-2021 | 12 | 3 | School1 | Building2 |
| 25-03-2021 | 12 | 4 | School1 | Building2 |
| 26-03-2021 | 12 | 2 | School1 | Building2 |
| 27-03-2021 | 12 | 4 | School1 | Building2 |
Hi deb_power123 ,
I'm afraid not. Font settings could only formatted in the Format pane and apply to the whole value fields instead of using conditional format.
If the bold font is necessary, we could only adjust the font size as blod entirely under Values tab.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
- amitchandak
Super User
deb_power123 , you have per row and per column. But that is school name and building
https://community.powerbi.com/t5/Desktop/Exclude-Certain-Subtotals-From-Matrix/m-p/724103
For the measure you can only display blank using is in scope
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
example
if(Isinscope(Table[SchoolName]) && not(isinscope[Building])) , blank(), [measure])
deb_power123Hi amitchandak
Will the above DAX work for Grand Total and Row Total in PowerBI table matrix? Because i think blank will make the measure value empty.
Is there any other way to remove the TotalOccupied row from table matrix view?Please suggest
v-yingjlCommunity Support
Hi deb_power123 ,
Currently in power bi the Value fields are shown in columns, not in rows, including the total value so that the row total value is only one row. You can create measures to hide the Occupied values in toal and change the total name:
Occupied = IF ( HASONEVALUE ( 'Table'[Occupied seats] ), SUM ( 'Table'[Occupied seats] ) & " | " & SUM ( 'Table'[TotalSeats] ), BLANK () )%Occupied = SUM ( 'Table'[Occupied seats] ) / SUM ( 'Table'[TotalSeats] )Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- deb_power123
Hi v-yingjl Thankyou for your reponse, i will try your said approach .Since 21 March and 27 march 2021 are weekends or Saturdays , is there a way I can mark the weekends Saturday and sunday in Red and Bold font .
so say I want to show 21 and 27 as red and bold font as highlighted below in yellow.Could you please suggest an approach to achieve it?
- v-yingjl
Hi deb_power123 ,
Currently we can just set the font color by conditional format. Create a measure like this:
Format = IF ( WEEKDAY ( MAX ( 'Table'[Date] ), 2 ) = 6 || WEEKDAY ( MAX ( 'Table'[Date] ), 2 ) = 7, "red" )Apply it for the previous two measures in conditional format:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- deb_power123
Hi v-yingjl Sorry for late reply.
1) Could you please suggest if we can order the dates in descending format in the .pbix file you sent me? Please suggest. At the moment it is in the ascending format of dates. It should be in order 27 March ,26March.....21 March 2021 :-
2) Also is there any property to align the values for occupied and % occupied to the extreme left of the grid boxes so that it looks more aligned.If you see now we have some spaces in the left , i want to move it to extreme left , how can i do it?
