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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
marsclone
Helper IV
Helper IV

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 to work with Power BI, but at work I can only use Power Pivot.

 

Now I would like to build a Balance Sheet as well. The examples I find on the forum use measures that are not available in Power Pivot.
Therefore, I would like to use the current P&L model for building a Balance Sheet as well. Only the subtotals are calculated differently.

 

I hope you guys can help me in creating this overview.
I have attached a Testfile and the end result looks like this.

 

P&L - Working 

Balance - Not Working 

 

Balance.png

Thank you and best regards,
Marcel

1 ACCEPTED SOLUTION

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

image.png

 

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

image.png

 

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.

 

View solution in original post

8 REPLIES 8
Fcoatis
Post Patron
Post Patron

Hi @marsclone Here is a proposed solution if you can change the layout as follows:

Fig1.png

marsclone
Helper IV
Helper IV

@v-kkf-msft 

Hi Winniz,

This is looking good! At the moment i'm on a holiday. When i'm back the first thing i do, is to test this.

I will let you know!

Thank you!

Kind regards

Marcel

selimovd
Super User
Super User

Hey @marsclone ,

 

your measures don't show any data because the relationship between the tables report balance and accounts balance is not active:

selimovd_0-1629118933924.png

 

Just activate the relationship with a double click and then "Make this relationship active":

selimovd_1-1629118994363.png

 

Then your measures will show values:

selimovd_2-1629119032715.png

 

In general an inactive relationship is more or less like a non existing relationship. The two tables were not connected without an active relationship. Always make sure the relationships fit.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

 

 

@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

@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?

@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?

 

Subtotals.png

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

image.png

 

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

image.png

 

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.

 

@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 

 

Balance - P&L.pngSubtotals.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.