Build Balance and P&L

Hi!

For our organization, with the help of fellow forum members, I have built a P&L and a Balance Sheet in recent years. However both are put together separately and in their own way.

The P&L fairly complex and the Balance Sheet simpler.

Now I would like to build the P&L in the same way as the Balance Sheet but ask myself if this is possible?

The subtotals of the P&L are calculated differently than those of the Balance Sheet. Also, the result of the P&L should be added to the equity in the Balance Sheet.

I hope you can help me further. I show the results in the image below.

I have also attached a test file.

Thanks and best regards,

Marcel

I modify your final code:

IF (
    MAX ( 'Report Balance'[Betreft] ) = "Balance",
    SWITCH (
        TRUE (),
        MAX ( 'Report Balance'[Sortgroup] ) = MAX ( 'Report Balance'[Group 1] ), [GROUP_1],
        MAX ( 'Report Balance'[Sortgroup] ) = MAX ( 'Report Balance'[Group 2] ),
            CALCULATE (
                SUMX ( ALLSELECTED ( 'Report Balance'[Sortgroup] ), [GROUP_1] ),
                FILTER (
                    ALL ( 'Report Balance' ),
                    'Report Balance'[Subtotal?] = 1
                        && 'Report Balance'[Sortgroup] < MAX ( 'Report Balance'[Sortgroup] )
                        && 'Report Balance'[Sortgroup] > Pre_Group2
                )
            ),
        [Total Balance]
    ),
    IF (
        MAX ( 'Report Balance'[Subtotal?] ) = 1,
        SUMX (
            FILTER (
                ALL ( 'Report Balance' ),
                [Betreft] = "P&L"
                    && [Sortgroup] < SELECTEDVALUE ( 'Report Balance'[Sortgroup] )
                    && [Subtotal?] = 0
            ),
            [Total Balance]
        ),
        [Total Balance]
    )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

20 Replies

Anonymous
Not applicable
4 years ago
Hello marsclone

Please check the attached file.
- marsclone
  Helper IV
  4 years ago
  Hello Anonymous
  
  Thank you for your work, only i don't see a difference? Is there something going wrong?
  - Anonymous
    Not applicable
    4 years ago
    Hello marsclone
    
    Could you please share the updated data in which I'll get Net Income value? Please share the expected output as well. According to my understanding, you need to add Result and Net Income. Isn't it?
Anonymous
Not applicable
4 years ago
Hello marsclone

If you check your data there are no values for Net Income. Once you updated the data then you can see the changes accordingly.

Thank You.
- marsclone
  Helper IV
  4 years ago
  Hi Anonymous
  
  You are correct that there are no values for Net Income, the values for "Gross Profit","EBITDA", .... , "Net Income" have to be calculated first. The current measure is not working properly, when this works then your measures come in place i think?
  
  The outcome looks like this
  
  Thank you!
  - Anonymous
    Not applicable
    4 years ago
    Hello marsclone
    
    Could you please confirm is it working properly or not? If there is any issue could you please share the pbix file after removing the sensitive information? Also, please share the updated file and expected result.
v-janeyg-msft
Community Support
4 years ago
Hi, marsclone

I spent a lot of time digesting your sample, but I am still confused about your ideal result.

In your sample data, capital5 and result are reversed, but even if it’s reversed, I have read your calculation logic, and it is just cumulative, so how did you get value 6580407?

As for why 0 appears in the p&l part, it is because the logic of your measure in the p&l part is wrong.

I can modify it for you, but I am confused. The value of your original data already has a negative number, which conflicts with the result you want. This will affect the results of meausure.

There are many problems with your measure, but if you don’t explain the contradictions I mentioned first, it will be difficult for me to modify it for you.

So can you explain it? So we can help you soon.

Below is the sample I used yours.

Best Regards,
Community Support Team _ Janey
Balance - P&L.pbix86 KB
- marsclone
  Helper IV
  4 years ago
  Hi v-janeyg-msft
  
  Thanks for your response and effort put into it.
  
  In the attached images I have tried to show the desired result. I have replicated these in Excel and have reversed the pluses and minuses here. So this does not match what is shown in Power BI.
  
  In the images below I have now adjusted this. These are imitations in Excel that I would also like to see in Power BI.
  
  As you indicate, first the logic of the measurement for the part of the P&L will have to be adjusted to arrive at the Net Income of -100,637.
  
  When this is successful, this Net Income will have to be added to the Result in the balance sheet.
  
  I hope that it is now a little clearer? Thank you very much for your help.
  
  Kind regards,
  Marcel
  - v-janeyg-msft
    Community Support
    4 years ago
    marsclone
    
    I will modify the measure to display correct Net Income, It will take some time, please wait.
    
    And I still can't understand:
    
    Your measure doesn't affect the accumulated value because they are all 0. So I am puzzled how your expected result came out...
    
    Best Regards,
    Community Support Team _ Janey