Matrix hide blanks

Question

Hi,&nbsp;I am looking for a solution, for matrix visual .&nbsp;&nbsp;I have a set of numbers categorised by hierarhy but some of categories has blank name but has some values , like example below for number 25 there is blank category name:&nbsp;I&nbsp;&nbsp;I am looking for a solution where I will be able to hide all rows with blank category name but keep them calculated on totals. Like in shown example, I would like to keep to total and subtotals be 118, but this row with blank to be hidden, so easy filter will not work.&nbsp;I realize that it can cause some misunderstanding when someone sum values from column and it will be different than subtotal/grandtotal.&nbsp;&nbsp;I will be grateful for any help.&nbsp;

v-cherch-msft · Answer

Hi&nbsp;Marcin&nbsp;You may add a blank measure and drag it to visual level filter. Then you may create a measure to get the total as below.Blank = IF(MAX(Table2[Column2])&lt;&gt;BLANK(),1)Sum =
IF (
    COUNTROWS ( Table2 ) = 3,
    SUMX ( ALL ( table2 ), Table2[Column3] ),
    SUM ( Table2[Column3] )
)&nbsp;Regards,Cherie

marcin · Answer

Hi Cherie,&nbsp;thank you for this reply, in this case specific case it works fine but I have created this small table as a simple demo for greater solution where I have 8 levels of hierachy and&nbsp; different count of rows&nbsp; in each level , so could you also help me how to solve that with dynamic number of rows , maybe any variable could work ?&nbsp;

marcin · Answer

I made a mistake in previous post I have deleted, data sholud look like that :&nbsp;Column1 Column2 Column3 ValueLevel1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp;Level31&nbsp; &nbsp; 10Level1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp;Level32&nbsp; &nbsp; 15Level1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Level1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp;Level34&nbsp; &nbsp; 15Level1&nbsp; &nbsp; &nbsp; &nbsp; Level22&nbsp; &nbsp;Level36&nbsp; &nbsp; 15Level1&nbsp; &nbsp; &nbsp; &nbsp; Level23&nbsp; &nbsp;Level37&nbsp; &nbsp; 20Level1&nbsp; &nbsp; &nbsp; &nbsp; Level24&nbsp; &nbsp;Level38&nbsp; &nbsp; 21Level1&nbsp; &nbsp; &nbsp; &nbsp; Level24&nbsp; &nbsp;Level39&nbsp; &nbsp; 24Level1&nbsp; &nbsp; &nbsp; &nbsp; Level25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Level1&nbsp; &nbsp; &nbsp; &nbsp; Level25&nbsp; &nbsp; Level30&nbsp; &nbsp;17&nbsp;I am sorry for previous mistake.&nbsp;Regards&nbsp;Marcin&nbsp;

marcin · Answer

This table may also look like this ( additional row at the bottom) where some data have less hierarchy levels and then there are like two blanks categories for one value.&nbsp;&nbsp;Column1 Column2 Column3 ValueLevel1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp;Level31&nbsp; &nbsp; 10Level1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp;Level32&nbsp; &nbsp; 15Level1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Level1&nbsp; &nbsp; &nbsp; &nbsp; Level21&nbsp; &nbsp;Level34&nbsp; &nbsp; 15Level1&nbsp; &nbsp; &nbsp; &nbsp; Level22&nbsp; &nbsp;Level36&nbsp; &nbsp; 15Level1&nbsp; &nbsp; &nbsp; &nbsp; Level23&nbsp; &nbsp;Level37&nbsp; &nbsp; 20Level1&nbsp; &nbsp; &nbsp; &nbsp; Level24&nbsp; &nbsp;Level38&nbsp; &nbsp; 21Level1&nbsp; &nbsp; &nbsp; &nbsp; Level24&nbsp; &nbsp;Level39&nbsp; &nbsp; 24Level1&nbsp; &nbsp; &nbsp; &nbsp; Level25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Level1&nbsp; &nbsp; &nbsp; &nbsp; Level25&nbsp; &nbsp; Level30&nbsp; &nbsp;17Level1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 45

Forum Discussion

Matrix hide blanks

4 Replies

Related content

Remove/Hide Blank/Empty Columns in Matrix

Hide blank values in hierarchy matrix

Remove Blank Rows in Matrix

PowerBI - Matrix table, hide values used only for a visual calculation

Matrix table column Hide