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  

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

        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.

         

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

        Best Regards,
        Community Support Team _ Janey