Forum Discussion
Formulas in rows of matrix / Custom formulas in table rows
I'm basically creating Profits & Losses statements where I need to calculate some rows with custom formulas for the final matrix visualization. Easy to do with Excel, not so much with Power BI. Do you have any ideas how to do it?
Sogeking,
I am not clear about your expected result. Please share sample data of your tables following the guide in this thread and post expected result here.
Regards,
Lydia
Okay, so here are some screenshots of a dummy project and the expected result.
First, the expected result in a quick and dirty Excel file:
A very basis P&L-like statement.
The key difficulties are the sales % and salaries % lines as well as the Marge on salaries and Special result lines. Of course, I want to recreate this table in Power BI, using a single matrix visualization with years as columns and those calculations as lines.
How to get there :
1 : The presentation table with the fields I want to include in the final P&L
2 : The Key table where I link accounts to type (field for the P&L) and to transactions
3 : My formula table where I want to create my custom formulas to populate my P&L. I want to be able to calculate those values as lines in the P&L. This table may lack the sales% and salaries%, but if a solution is found, I would just need to duplicate it for all the formulas I wish to use.
4 : The transactions table I use to calculate all the aggregates for the P&L
Hope it's a bit clearer. Dummy project for reference :
https://nofile.io/f/PYwOEhO9MVx/Dummy.pbix
