Customizing Matrix Visualization for Income Statement

[waltmiller]

Hi, 

I'm creating an income statement for my organization via a Power BI Matrix. The rows are a breakout of our Ledger Account Structure in 4 subsequent parents (L2Parent as "Ledger Account", L3Parent, L4Parent, L5Parent) and then the nested Ledger numbers and Spend/Revenue Category numbers beneath them - see the first  image below. The hierarchy is intrinsic in the data.  The columns/Values are all calculations performed by Measures.

 Due to the limitations of BI Matrixes/my skillset, I'm running into 2 complications:

1) Since some of our L4Parents do not have L5Parents (and a couple L3s which do not have L4), there will be blank values as the matrix is drilled down. On the left is an example where it succesfully works, and on the right is an example where it does not. Is there a way to skip a row level if that field is blank? 

2. Our income statement in a different program (which has its own issues, hence this initiative) can be filtered down to a cell level, meaning much more ability to create a custom row structure. Thus, we have custom sub-lines such as the "Total Revenue and Sources" option below on the left. Is there a way to replicate that in this BI Matrix (right) and have it sit between the "Sources" and "Expense" L2Parents?

 

Thank you kindly!

[tharunkumarRTK · Accepted solution]

@waltmiller 

Point No: 2:
There is an option for you to keep a unique label for each row sub total level. However it would be same across that row level, wont change as per the value in that row. If that does not solve your purpose, then one other option is to create a custom column in your table and put that as another row level. 
I would suggest you to watch this video, as it also covers the latest features of matrix visual 
https://youtu.be/GSv3Zwtw8cM?si=ZQavqiOxWoRb9xQq

Point 1: You can do some transformation logic to replace null/blank values with the next level row header. Then the blank value wont appear in the visual.. Or, incase if your requirement is to filter out the blank records then keep in mind that it will filter out the data that corresponds the blak value.

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun