Calculated Rows P&L

[marsclone]

I have been greatly helped by another user but i didn't find the right solution

This is my testfile

Test

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!

[Fcoatis]

@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

[marsclone]

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.

TEST 

I really hope you can help me with this? Very thankful in advance.

Kind regards

Marcel

[v-lili6-msft]

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]

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

[Anonymous]

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] )

[marsclone]

Thank you for your answer, but can you take a look at the test file?

Is your solution suitable for my situtation?

[Anonymous]

Ah ok I see that your data is in a narrow table. My measures were made assuming each item had its own column. For this format of data the measures would be as follows

Revenue1 =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Revenue1" )

Revenue2 =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Revenue2" )

Total Revenue = [Revenue1] + [Revenue2]

Costs1 =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Costs1" )

Costs2 =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Costs2" )

Total Costs = [Costs1] + [Costs2]

EBITDA = [Total Revenue] - [Total Costs]

Decrease1 =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Decrease1" )

EBIT = [EBITDA] - [Decrease1]

Interest1 =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Interest1" )

EBT = [EBIT] -  [Interest1]

Taxes =
CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Taxes" )

Net Result = [EBT] - [Taxes]

Hope this helps

[marsclone]

Yes! This is the way to calculate the desired outcomes.

But if want to achieve the desired report, i (think) have to use (calculated) columns?

How do we achieve that?

Thank you for your support.

[Stachu]

if the values are in rows, then you will need a new table ( or 2 - one with unique measures, and second with calculation logic)
have a look here
https://community.powerbi.com/t5/Desktop/Create-calculated-row/m-p/440777/

[marsclone]

@Stachu

I've studied this post, but i really don't know how to apply this on my data?

Is it possible to have a look at my "test" file?

[v-lili6-msft]

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