Forum Discussion

Helper V

7 years ago

Matrix hide blanks

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

Microsoft Employee

7 years ago

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
Helper V
7 years ago
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
  Helper V
  7 years ago
  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
    Helper V
    7 years ago
    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