Build P&L mapping subcategories Matrix visual

[Anonymous]

Dear all, I'm trying to "export" a P&L from excel to PowerBI, inparticular my database in Excel for PowerBi is the following:

And I would like to have in PowerBI the structure with the possibility to have and enlarge sub categories. I therefore created the following mapping:

And I linked in PowerBI the key. However, when I create the matrix with category, 1.subcategory, 2.subcatecory, 3.subcategory, I can enlarge all the categories, even the ones that does not have subcategories (the highlighted in grey in the mapping) and I see the following:

Is it possible to give the possibility to enlarge the categories (+) only to certain rows? Or should I change the mapping)

I also tries to delete the grey highlighted cells in excel, but then I will have the same problem of being able to enlarge every rows but just without having the title (therefore only displaying empty rows).

Once this is solved, I would like also to show the rows in order, how can I do it?

Thank you very much in advance!

[SamWiseOwl]

Hi @Anonymous If I am understanding what you are asking:

• You don't need those headers rows as the Matrix will still display them in the highest level.
  • So Variable - variable - variable isn't required.
• Power BI will automaticall hide cells with no data, so you could create a Measure like so:
  • Sales = If( Sum(table[sales]) = 0, Blank(), Sum(table[sales]))

[Anonymous]

Hi @SamWiseOwl. Thank you for your reply.

I need all the headers because I want the possibility to enlarge to 3.Subcategory therefore a need a mapping for all the subcategories even for the lines that have only 1.Subcategory (Variable in this case).

I want to exclude the possibility to enlarge until 3.Subcategory for the lines that don't have that subcategory.

Moreover, for example for operating expenses, it is mapped Operating expenses - Operating expenses - Operating expenses - Operating expenses for the same reason as above, but then when I include it in the matrix I have:

Operating expenses

 - Fixed

 - Variable

 - Operating expenses again

I don't know how to solve this or how should I mapp instead

[SamWiseOwl · Accepted solution]

Use a measure to hide the row, if they have no subcategory they will have no Value associated with the row. Here for example I have two items:

If I filter the data the item dissapears: