Forum Discussion
Build P&L mapping subcategories Matrix visual
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!
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:
3 Replies
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]))
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
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:
- You don't need those headers rows as the Matrix will still display them in the highest level.