Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marsclone
Helper IV
Helper IV

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

 

Testfile 

 

Balance - P&L.pngSubtotals.png

 

1 ACCEPTED SOLUTION

@marsclone 

 

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

vjaneygmsft_0-1638525737972.png

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

View solution in original post

20 REPLIES 20
v-janeyg-msft
Community Support
Community Support

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? 

vjaneygmsft_0-1638351326454.png

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.

vjaneygmsft_1-1638352746744.png

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

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

 

P&L - Right.png

Balance - P&L.png

 

 

@marsclone 

 

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

vjaneygmsft_0-1638525737972.png

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

Hi @v-janeyg-msft 

 

Thank you very much for the modification of the relevant measurement. The subtotals are now calculated correctly. Really great!

 

I could also use the original measurement in Excel Power Query. Last few days I tried to apply your modification in Power Query as well. I just can't get this to work.

 

I also can't see, where exactly it goes wrong? Probably it has to do with the part "selectedvalue"?

 

Below I have the Excel file. I hope, that you can see, where exactly it goes wrong.

 

Thank you very much in advance!

Kind regards,

 

Test Power Query  

@marsclone 

 

The excel file you shared is invalid, we can't see the data. 

This is another question. Excel powerquery and powerbi power query use M language, but powerbi report using Dax language. Dax has context, M has no, it's different.

If necessary, please open another case to describe your problem and give the sample data you want.

vjaneygmsft_0-1638929042388.png

 

If your original problem has been solved, Please mark my reply as solution. Thank you very much.😊

Best Regards,
Community Support Team _ Janey

 

Hi @v-janeyg-msft 

 

Thanks again for all the help you give me!


I managed to modify the measurement myself so that it can be used in Power Query.

 

I would love to put this forum post on resolved 🙂
But i have two more questions 🙂

 

Now that we have calculated the "Net Income" (-100,636.50), I would like to add it to the "Result" (-6,479,770.48) in the balance sheet. So that this total comes out to 6,580,406.98. This is where the balance sheet comes into balance (Assets 60,310,820.79 = Liabilities 60,310,820.79). Is this possible?

 

My second question is, I have in the Balance Sheet and P&L main groups (Group) and I would like to deepen them with subgroups (Subhoofd). However when I do this the main groups which are filled with the measurement (Non-Current Assets/Current Assets/Sharholders Equity/Services Total and onwards) are also subdivided again into subgroups while these should only remain (sub)totals.

 

Can this be prevented?

 

The new test file: Testfile 

 

Thanks again for all the help!

Kind regards,
Marcel

 

@marsclone 
Please post a new post for new questions and full description of your problem.

@marsclone 

 

I will modify the measure to display correct Net Income, It will take some time, please wait.

And I still can't understand:

vjaneygmsft_0-1638512880045.png

vjaneygmsft_1-1638513261609.png

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

Anonymous
Not applicable

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.

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

 

P&L - Right.png

 

Thank you!

Anonymous
Not applicable

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.

Hello @Anonymous 

 

The expected result should look like this

 

P&L - Right.png

 

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?

 

Kind regards

Marcel

Anonymous
Not applicable

Hello @marsclone 

Please check the attached file.

Hello @Anonymous 

 

Thank you for your work, only i don't see a difference? Is there something going wrong?

 

Balance - P&L new.png

Anonymous
Not applicable

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?

Hi @Anonymous 

 

Ultimately, Net Income must be added to the Result in the balance sheet.

 

However, to accomplish this, the P&L must first be properly calculated. The current measurement ensures that the balance sheet is built up correctly, however the P&L is not.

 

The subtotals of the P&L must be determined differently. The desired result looks like this:

 

P&L - Right.png

 

If the P&L is built properly, then the Net Income should be added to the result in the balance sheet. The desired result then looks like this:

 

Balance - P&L.png

 

I hope, you can help me with this. Thanks again for your help!

 

Regards,
Marcel

 

Hi, @marsclone 

 

Could you please share the updated sample data? Otherwise we can hardly help you.

 

Best Regards,
Community Support Team _ Janey

Hi, @v-janeyg-msft 

 

I am very grateful for all the help, but there is no updated data.
The data as it is in the test file is the available data.

 

As I mentioned in my post of 11-17-2021, the subtotals of the balance sheet are calculated correctly and those of the p&l are not.
This should be solved first. I have illustrated by means of a picture what the desired result should be.

 

I hope that you can help me with this?

Kind regards,

Marcel

@marsclone 

 

Sorry, due to COVID-19 screening, I may not be able to give you an answer until next Wednesday.

 

Best Regards,
Community Support Team _ Janey

Hi @v-janeyg-msft 

No problem, i hope you can help me further.

Stay well!

Regards Marcel

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.