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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 280 members 3,210 guests
Please welcome our newest community members: