Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Remove empty row headers from matrix

Dear PowerBI experts,

 

I hope one of you has a solution to the following. 

 

I am building a P&L report. The rows are derived from a multi-level account hiearchy where level 1 is the highest level. Lower levels are not always populated, apart from the (GL) AccountName where I also have the values from. Is there any way to hide the empty account levels from being shown in the matrix row headers?

 

My data model looks as following

 

Table: AccountStructure

 

AccountLevel1

AccountLevel2

...

AccountName

 

Table: Facts

 

AccountName

Value

 

Matrix

Rows: AccountLevel1, AccountLevel2, ...,AccountName

Value: Value

 

To make it visual, in the following example, I would like to remoce the +/- icon from Total gross Profit:

 

 

 ___

Nikita

Need Help

4 Replies

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    Anonymous you can put a fiter against your values like below which will show only values greater than o so blank value column/row header will not appear

     

     

    and to remove +/- icon, under the row headers section, please off the below selection

     

     

    thanks. let me know if you are able to resolve your issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your rpely.

       

      In my example, the values are actually not blank or 0. It is the row header that is blank. Unfortunately, enabling the filter on either the AccountLevel3 or Value to filter out zeros and blanks does not make a change.

       

      Update: Adding a filter on AccountLevel3 to filter out empty records hides the whole row completely including the higher level, but that is not what I want...

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

     

    The hiearchy columns have relationships with each other as the lower level is in the scope of its higher level. When a higher level is hidden, its lower level rows will be hidden too. Based on my test, I couldn't find a solution or workaround to this requirement currently.

     

    Regards,
    Community Support Team _ Jing

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

     

    There is an excellent video here:  https://www.youtube.com/watch?v=W4zEYkDC3Tw that shows how to handle displaying hierarchical data in the context of a P&L statement.  The part that specifically addresses your issue is at 40:00 minutes, but you will likely need to watch the entire video for context.

     

    NOTE:  Refer to Matt's website at https://exceleratorbi.com.au/build-a-pl-with-power-bi/ where he provides the DAX formulas, which are slightly different than exactly what he used in the video, and worked better for my application.