How to exclude or hide a specific row subtotal in a Table Matrix visualization?

[deb_power123]

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

[v-yingjl]

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   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?

[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  awesome approach, can I also make bold font along with red color in your above stated forumla? please suggest...

Is there a way to make it both red and bold ...so it looks more distict...please suggest 

[v-yingjl · Accepted solution]

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.

[deb_power123]

Hi @v-yingjl 

I have a table location and I want to hide the total of seats used column[Total Used] and show only total for [%Occupied] .

I am able to do so using the DAX you suggested in above thread but it works correctly only when there are multiple entires of building floor for a location but when it is single location then it is showing both total of seats used column[Total Used] and show only total for [%Occupied].

Below is the DAX to hide the Total Used values in the visual:

Total Used = IF(
HASONEVALUE( 'location'[seats used]),
SUM('location'[seats used]) & "|"
& SUM('location'[seats available]),
BLANK()

)

%Occupied = SUM('location'[seats used])/SUM('location'[seats available])

My present visual looks like below :-

Here I want to get rid or hide the total of Used  which is highlighted in yellow. The DAX query  is only working when there are multiple entries for building floor column corresponding to its location.

Input data :-

Expected output:-

Could you please suggest how and what changes do i need to implement in the above DAX to hide the total of  used column[highlighted in yellow]?

Input source is same as the above .pbix file you provided.

kind regards ,

sameer

[deb_power123]

Thankyou that worked

[amitchandak]

@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_power123]

Hi @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