Forum Discussion

marsclone's avatar
marsclone
Icon for Helper IV rankHelper IV
4 years ago
Solved

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...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

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

    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

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity 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

  • marsclone's avatar
    marsclone
    Icon for Helper IV rankHelper 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's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity 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

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

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

      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

      • marsclone's avatar
        marsclone
        Icon for Helper IV rankHelper IV
        4 years ago

        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