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

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

 

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

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

 

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

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