Matrix hide blanks

[Marcin]

Hi,

I am looking for a solution, for matrix visual . 

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:

I 

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. 

I realize that it can cause some misunderstanding when someone sum values from column and it will be different than subtotal/grandtotal. 

I will be grateful for any help. 

[v-cherch-msft]

Hi @Marcin

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])<>BLANK(),1)

Sum =
IF (
    COUNTROWS ( Table2 ) = 3,
    SUMX ( ALL ( table2 ), Table2[Column3] ),
    SUM ( Table2[Column3] )
)

Regards,

Cherie

[Marcin]

Hi Cherie,

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  different count of rows  in each level , so could you also help me how to solve that with dynamic number of rows , maybe any variable could work ? 

[Marcin]

I made a mistake in previous post I have deleted, data sholud look like that :

Column1 Column2 Column3 Value
Level1        Level21   Level31    10
Level1        Level21   Level32    15
Level1        Level21                   12
Level1        Level21   Level34    15
Level1        Level22   Level36    15
Level1        Level23   Level37    20
Level1        Level24   Level38    21
Level1        Level24   Level39    24
Level1        Level25                   12
Level1        Level25    Level30   17

I am sorry for previous mistake.

Regards

Marcin 

[Marcin]

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. 

Column1 Column2 Column3 Value
Level1        Level21   Level31    10
Level1        Level21   Level32    15
Level1        Level21                   12
Level1        Level21   Level34    15
Level1        Level22   Level36    15
Level1        Level23   Level37    20
Level1        Level24   Level38    21
Level1        Level24   Level39    24
Level1        Level25                   12
Level1        Level25    Level30   17

Level1                                        45