Creating (sub)totals P&L

[marsclone]

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!

[v-lili6-msft]

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

Step 2:

Drag Hierarchie TOTAL into Rows of matrix

Note:It must be in first level

Step 3:

Expand all down one level in the hierarchy

Result:

Best Regards,

Lin

[marsclone]

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

[v-lili6-msft]

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

Step 2:

Select all columns and replace values

Step 3:

Add custom column

Step 4:

Move column Total Revenue to the third column

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

Step 5:

Rename column

Step 6:

Transpose the table

Result:

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

[marsclone]

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