Forum Discussion
Formulas in rows of matrix / Custom formulas in table rows
I started using Power BI a few days ago and so far it looks great, but I feel that it is still a bit limited/I don't know enough to make what I want.
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?
I looked online but so far haven't found a solution.
My current idea is to try and do the following : using a table with my custom formulas and link them as rows of my P&L. It would look like this :
Table 1 : Formulas
Column A /// Column B
Custom_Income /// = SUM([P&L]P&L_Accounts; = 70)
Table 2 : P&L accounts :
Column A /// Column B
15 /// 1000
70 /// 500
Table 3 : P&L Structure
Column A /// Column B
Expense /// 2018_Value
Custom_Income /// 500 [FROM Table 1]
Thanks for the tips !
7 Replies
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,
LydiaOkay, 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
- Bump as I got no answer.
PaulDBrownCommunity Champion
can you provide sample data or PBIX file and a depiction of the expected outcome?
