Forum Discussion
Calculated Rows P&L
I have been greatly helped by another user but i didn't find the right solution
This is my testfile
I want to create a P&L in Power BI. I have my costs and revenues in one column which are categorised.
My P&L looks now like
Revenue1
Revenue2
Cost1
Cost2
Decrease1
Interest
Taxes
I would like to add some subtotals, like
Revenue1
Revenue2
Total Revenue
Cost1
Cost2
Total Cost
EBITDA
Decrease1
EBIT
Interest
EBT
Taxes
Net Result
Is this possible? Thank you!
11 Replies
hi,@marsclone
what is the formula to calculate the "EBITDA","EBIT";"EBT";"NET RESULT".
please share your expected output, you can share it by excel or screenshots.
Best Regards,
Lin
Hi Lin,
Thank you for your support! The formulas looks like:
Total Revenue = Revenue1 + Revenue2
Total Cost = Cost1 + Cost2
EBITDA = Total Revenue - Total Cost
EBIT = EBITDA - Decrease1
EBT = EBIT - Interest
Net Result = EBT - Taxes
Regards Marcel
So this is possible both with measures or with columns, I will post measures however if you need to use it for slicers you will need columns. Also depending on the size of your table you may want to use columns as it may speed up your report.
You will need to change "Table" to whatever your table is called
Total Revenue = SUMX ( Table, Table[Revenue1] + Table[Revenue2] )
Total Cost = SUMX ( Table, Table[Cost1] + Table[Cost2] )
EBITDA = [Total Revenue] - [Total Cost]
EBIT = SUMX ( Table, [EBITDA] - Table[Decrease1] )
EBT = SUMX ( Table, [EBIT] - Table[Interest] )
Net Result = SUMX ( Table, [EBT] - Table[Taxes] )
Thank you for your answer, but can you take a look at the test file?
Is your solution suitable for my situtation?
hi,@marsclone
After my research, I'm afraid it couldn't achieve that put these different kinds of subtotals into one visual for now .
You may try to add these subtotals by measures in the second visual and they also interact with each other.
Best Regards,
Lin
marsclone , v-lili6-msft Yes it is possible.
See how.
Assumptions (see in PowerQuery): Revenue values are positive. All others negative.
Best regards
Fábio Coatis
Hi Fcoatis!
I hope you are doing well?
I want to thank you after all this time for your solution. Your solution is in the end the best option for me.
Personally i work with Power BI, but in my work i only can work with Power Pivot/Power Query.
And your solution is the only one that also works in this situation.
After all this time i also would like to build a balance sheet in the same way. The question is, is that possible?
I tried to use your measures, but in the case of the Balance the subtotals work different.
I've added a testfile and the endresult should look this.
I really hope you can help me with this? Very thankful in advance.
Kind regards
Marcel
