Forum Discussion
Helper IComplicated Financial Reporting in Power BI
I have a user request where we have a Matrix of financial data, where we group items by Gross Revenue items and Expense items, there is a need to total the Gross Revenue items together and then deduct one of the Expense groupings from this Gross Revenue which would give us Net Revenue and display this below the Expenses row in the Matrix.
Since this is on-the fly calculations I am completely unsure how to even make this happen or if it's possible at all. I've attached a screenshot of the Matrix where I manually typed in the Net Revenue row to show what we are looking for.
What we want is a new Net Revenue row (in Blue) which is the Gross Revenue total (in Green) minus the Expenses 1 total (in Red).
Notice that the matrix will then have to continue with additional Expense lines (Expenses 1 and Expenses 2) and at the end of these, we will want another calculated row, similar to Net Revenue. It would be Net Revenue minus Expenses 2 and Expenses 3, This would be Net Profit.
Is this even possible? Thank you for any feedback.
Hi mgiusto ,
Yes, this is definitely possible in Power BI, though it requires a more customized setup.
One approach is to create a separate measure for each line item in your financial statement, including calculated rows like Net Revenue and Net Profit. Then, instead of placing fields in the rows of your Matrix visual, you'll:- Leave the Rows section of the matrix blank
- Add all of your custom measures to the Values section
- Keep your existing fields (e.g., Month) in the Columns section
- Navigate to the matrix visual settings and toggle the option "Switch value to rows"
This will display each of your measures as a row in the matrix, which matches the layout you're looking to achieve and includes custom calculations that do not exist in the underlying data.
If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
Connect with me on LinkedIn
Check out my Blog
Going to the European Microsoft Fabric Community Conference? Check out my Session
4 Replies
- SamsonTruong
Super User
Hi mgiusto ,
Yes, this is definitely possible in Power BI, though it requires a more customized setup.
One approach is to create a separate measure for each line item in your financial statement, including calculated rows like Net Revenue and Net Profit. Then, instead of placing fields in the rows of your Matrix visual, you'll:- Leave the Rows section of the matrix blank
- Add all of your custom measures to the Values section
- Keep your existing fields (e.g., Month) in the Columns section
- Navigate to the matrix visual settings and toggle the option "Switch value to rows"
This will display each of your measures as a row in the matrix, which matches the layout you're looking to achieve and includes custom calculations that do not exist in the underlying data.
If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
Connect with me on LinkedIn
Check out my Blog
Going to the European Microsoft Fabric Community Conference? Check out my Session
mgiustoSamson,
Would this mean just having Gross Revenue, Net Revenue & Net Profit as my measures and I would lose the ability to have a matrix with the hierarchy shown in my screenshot?
We would like to maintain that hierarchy, but if it's not possible maybe what I can do is create what you are talking about as another visual below my matrix just showing these new calculated measures with the values that do not exist in the data???
Is this how I should go about it or is there a way to do what you are saying within the matrix?Michael
- mgiusto
Samson,
You my friend are amazing! You nailed it exactly!
The only issue is I lose the hierarchy ablility to expand or collapse out the rows like I was able to before in this matrix. I also have no way of formatting the total rows like Gross Revenue and Net Revenue to make them stand out. Unless you know how to do what I am looking for. Let me know.
But really happy I can show something close to what we are looking for. Thank you so much!
Michael
- SamsonTruong
Hi mgiusto ,
Unfortunately, with this method, you do lose the ability to expand or collapse rows. However, you are still able to apply some formatting to make the total rows standout. You can apply formatting in the visualizations pane in the "Specific column" section. While the formatting options are more limited in this layout, what I've done in the past is change the font color for key rows to make them more visually distinct.