cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marsclone Regular Visitor
Regular Visitor

Creating (sub)totals P&L

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!

4 REPLIES 4
Community Support Team
Community Support Team

Re: Creating (sub)totals P&L

Hi@ marsclone

After my research, you can do these follow my steps like below:

Step 1:

Transpose the table and click use first row as headers

8.png

Step 2:

Select all columns and replace values

9.png

Step 3:

Add custom column

10.png11.png

Step 4:

Move column Total Revenue to the third column

Then click Transform->Use First Row as Headers-> Use Headers  as First Row

12.png

Step 5:

Rename column

 

Step 6:

Transpose the table

 

Result:

13.png

 

You can add Total Cost EBITDA by using the same method.

 

Here is Demo,please try it

https://www.dropbox.com/s/cntlszh6wzwu7rn/Creating%20%28sub%29totals%20P%26L.pbix?dl=0

 

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
marsclone Regular Visitor
Regular Visitor

Re: Creating (sub)totals P&L

Hi Lin,

 

Thank you for your answer! I have been trying your solution, but my situation is more complex i think?

My data looks like the test file i have added.

 

Test

 

Does your solution works also then?

 

Kind regards

Marcel

Community Support Team
Community Support Team

Re: Creating (sub)totals P&L

Hi@ marsclone

After my research on your file, you can do these follow my steps like below:

Step 1:

Add a Hierarchie TOTAL  column in table P&L

Hierarchie TOTAL = 
IF (
    SEARCH ( "Revenue", 'P&L'[Ertragslage], 1, 0 ) > 0,
    "Revenue",
    IF (
        SEARCH ( "Cost", 'P&L'[Ertragslage], 1, 0 ) > 0,
        "Cost",
        IF (
            SEARCH ( "Decrease", 'P&L'[Ertragslage], 1, 0 ) > 0,
            "Decrease",
            IF (
                SEARCH ( "Interest", 'P&L'[Ertragslage], 1, 0 ) > 0,
                "Interest",
                IF (
                    SEARCH ( "Test", 'P&L'[Ertragslage], 1, 0 ) > 0,
                    "Test",
                    IF ( SEARCH ( "Taxes", 'P&L'[Ertragslage], 1, 0 ) > 0, "Taxes" )
                )
            )
        )
    )
)

1.png

Step 2:

Drag Hierarchie TOTAL into Rows of matrix

Note:It must be in first level

2.png

Step 3:

Expand all down one level in the hierarchy

3.png

Result:

4.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
marsclone Regular Visitor
Regular Visitor

Re: Creating (sub)totals P&L

Hi Lin,

This works, but partly.

The next step is to calculate the "EBITDA","EBIT";"EBT";"NET RESULT"?

Thank you for your support!

Regards Marcel