Forum Discussion

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

Subtotals Balance Sheet

Hi, In the past, I have been very well helped by a fellow forum member in building a P&L. The nice thing about this model is, that it can be applied in Power BI and Power Pivot. Privately I like t...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi marsclone ,

     

    Please create two columns:

     

    Group 1 = 
var Pre = 
    MAXX(
        FILTER(
            'Report Balance',
            'Report Balance'[Sortgroup] < EARLIER('Report Balance'[Sortgroup])
                && 'Report Balance'[Subtotal?] = 1
        ),
        'Report Balance'[Sortgroup]
    )
return 
    IF(
        'Report Balance'[Subtotal?] = 1 && Pre <> 'Report Balance'[Sortgroup] - 1,
        'Report Balance'[Sortgroup]
    )
    Group 2 = 
IF(
    'Report Balance'[Subtotal?] = 1 && 'Report Balance'[Group 1] = BLANK(),
    'Report Balance'[Sortgroup]
)

     

    Then create the following measures:

     

    GROUP_1 = 
var Pre_Group1 = 
    MAXX(
        FILTER(
            ALL('Report Balance'),
            'Report Balance'[Group 1] < MAX('Report Balance'[Group 1]) 
        ),
        'Report Balance'[Group 1]
    )
var Group1_Value = 
    CALCULATE(
        [Total Balance],
        FILTER(
            ALL('Report Balance'),
            'Report Balance'[Sortgroup] < MAX('Report Balance'[Sortgroup]) && 'Report Balance'[Sortgroup] > Pre_Group1
        )
    )
return 
    IF(
        MAX('Report Balance'[Sortgroup]) = MAX('Report Balance'[Group 1]), 
        Group1_Value
    )
    Measure = 
var Pre_Group2 = 
    MAXX(
        FILTER(
            ALL('Report Balance'),
            'Report Balance'[Group 2] < MAX('Report Balance'[Group 2])
        ),
        'Report Balance'[Group 2]
    )
return     
    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 the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

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

selimovd 

Hi Denis,

Thank you for your reply. 
That the relationship was inactive, i didn't noticed. Thank you for that. 
The biggest problem is still the different subtotals that have to be calculated. 
At this moment it is a running total, but is not correct. 
For the right subtotals please see the screenshot. 
I hope you can help me?

Kind regards

Marcel

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

marsclone The groups that have different results seem to be missing in the table "Accounts Balance". At least Assets, Result and Shareholders Equity are missing.

 

Should that be like this?

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

    selimovd 

     

    These are subtotals calculated according to the "report balance" table.
    This method works when creating a P&L, but not for the balance sheet.
    I am looking for a method in which the subtotals can be calculated and displayed in this way.

     

    I wonder if this is possible?

     

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi marsclone ,

       

      Please create two columns:

       

      Group 1 = 
var Pre = 
    MAXX(
        FILTER(
            'Report Balance',
            'Report Balance'[Sortgroup] < EARLIER('Report Balance'[Sortgroup])
                && 'Report Balance'[Subtotal?] = 1
        ),
        'Report Balance'[Sortgroup]
    )
return 
    IF(
        'Report Balance'[Subtotal?] = 1 && Pre <> 'Report Balance'[Sortgroup] - 1,
        'Report Balance'[Sortgroup]
    )
      Group 2 = 
IF(
    'Report Balance'[Subtotal?] = 1 && 'Report Balance'[Group 1] = BLANK(),
    'Report Balance'[Sortgroup]
)

       

      Then create the following measures:

       

      GROUP_1 = 
var Pre_Group1 = 
    MAXX(
        FILTER(
            ALL('Report Balance'),
            'Report Balance'[Group 1] < MAX('Report Balance'[Group 1]) 
        ),
        'Report Balance'[Group 1]
    )
var Group1_Value = 
    CALCULATE(
        [Total Balance],
        FILTER(
            ALL('Report Balance'),
            'Report Balance'[Sortgroup] < MAX('Report Balance'[Sortgroup]) && 'Report Balance'[Sortgroup] > Pre_Group1
        )
    )
return 
    IF(
        MAX('Report Balance'[Sortgroup]) = MAX('Report Balance'[Group 1]), 
        Group1_Value
    )
      Measure = 
var Pre_Group2 = 
    MAXX(
        FILTER(
            ALL('Report Balance'),
            'Report Balance'[Group 2] < MAX('Report Balance'[Group 2])
        ),
        'Report Balance'[Group 2]
    )
return     
    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 the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

      Best Regards,
      Winniz

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

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

        v-kkf-msft Dear Winniz,

         

        Your solution helped me a lot to build a balance for our organization.
        Now I would also like to build a P&L in this way.
        The question is whether this is possible? Your method is easy to apply, both in Power BI and in Power Pivot.

        What might be a problem is that the result of the P&L should be added to the equity to balance the balance sheet?

        I hope you can help me with this.

        Thank you very much in advance for your help!

        Kind regards,
        Marcel

        Testfile Balance - P&L