Power BI: The Balance Sheet
ฝัง
- เผยแพร่เมื่อ 30 ก.ค. 2024
- Chris Barber shows how to build out a balance sheet in Power BI. This key financial report shows what a company owns (Assets) and what it owes (Equity and Liabilities). Chris uses data modelling, DAX and data visualisation and, in just a few clicks, explores this balance sheet data in Excel.
The dataset and Power BI file that Chris will demo in the session are at github.com/MarkWilcock/lbag-o...
Thanks Chris, that is outstanding. So much easier to understand than anything I have seen elsewhere. I look forward to implementing it
A tremendous piece of work, Chris. Much appreciated for posting it.
Thanks Rob
This is fantastic! Thank you for sharing the whole process instead of teasing the report and then putting the details behind a paywall like so many other content creators do.
Thanks David
i have a very complex data structured in format excel sent by one of the mnc i applied for its trial balance data contain fact and dim but cant figure out few things mainly perhaps because im from different bg glad if you anyone guyz help me solve and understand this please
Super understanding of Power BI. Sharing is appreciated.
I’ve been looking for a video to show to the finance team within my organisation to explain what the BI team are going to achieve. This is by far the best example of balance sheet production I have seen. Thank you.
Thank you Christopher. This is really nice feedback!
@@chrisbarber639 hey chris great work. Is there anywhere to ask question on this?
This video is so well put together and succinct. Thank you for sharing this.
Thanks Mike
Fabulous! Loads of suggestions in just 20 minutes. Now let's see if I can reproduce it in 20 minutes :)
Great Chris! Soundvolume, in comperison to the P&L vid, was spot on. Hex codes in columns. Didn´t know that one. Learning every day so to speak:) Thanks for sharing!
Thanks you! Glad it was useful
Amazing. This is exactly what I need! It's a bit too fast when talking about DAX calculations as I couldn't remember which variable is from which table. But, I have got the overall idea on the logic of working it out! Thank you so much!
Thanks Selina for the feedback. Always working on trying to improve.
What an awesome presentation!
Thanks Brian
Thank you for the video. It’s really helpful and simple to understand ❤
Great presentation!
Thanks Joanne
Fantastic explanation ! good job
Thanks Adrian
very well explained, thanks.
Thank you Kamran
this is amazing, truly awesome
Thanks for your sharing.
Thank you! Great tutorial.
Thank you Rafael
Thank you so much, well explained..
Hi Chris - thanks for the brilliant video on how to create a balance sheet.
Videos such as yours are a great help to me as I find transitioning from years of SQL development to PBI not intuitive. :)
I was trying to download the resources to accompany it and I received a SharePoint error - This link has been removed. Sorry, access to this document has been removed. Please contact the person who shared it with you.
Cheers Anthony
Hi Chris, thank you very much
Thanks for sharing, helped me alot! :)
No worries Youri - glad it was helpful
coalesce with three arguments is great
Thanks!
Thank you for this. It is perfect! Question, I set ours up and above the level 1 and level 2 categories, there is a total line. I don't want it there. There is no blank above it in the Balance Sheet Layout spreadsheet... Any help is appreciated! Thank you!
Excellent work, I was able to replicate it with data from adventure work 2017, to publish it in Spanish. I will refer to this video as a source of knowledge. Greetings
Thank you for the feedback
This is so interesting Chris
Thank you Meryem. Hope you find it useful
Manm your an angel! Thank you so much!
Thanks Murilo
Wow! This is amaizing
Thanks Abdan!!
Great video. But when I add the Balance Sum measure to the Matrix Table that shows Level 1 & 2, those said information disappears. Have you come across it and is there a fix?
Thanks for sharing this. Does this process support drill through to the transactions?
Amazing Video!!! I am trying to combine the learnings from the video to a table I have in my company, quick question, how would you go about getting the initial share capital (assuming it has not changed in years) so when you take a desired year to filter it gets included?
Great Chris! What a superb example on PBI for B/S. Can we get some more detailed examples on same topic by having B/S based on entity and region and consolidation concept. Thanks a lot. Sajid Mahmood
Hi Sajid. Thanks for the feedback. This is a topic I've not prepared yet, but is something I'll look to include on a future talk on this subject.
Thanks for sharing Chris, its a great approach for financials in Power BI.. have you tried using calculation groups at all for financial statements?
Thanks!
I haven't personally, but I've seen it done and had conversations about a couple of ways they could be used.
There's definitely an argument for and against depending on the scenario. Did you have a particular approach or way of using them you had in mind?
Thanks for sharing
This is showing rolling up to May 2021, how can I see January 2020 to Dec 2020 or any other period?
Should I put slicer or filter?
Yes- you're right add a slicer or filter
Thanks Chris, this is excellent and solves several challenges for me carrying out this type of analysis. The one thing that I am struggling with is the sorting of the columns using the level orders. In particular, Level 1 does not work with my database which appears to be because the Level 1 column includes several blanks (same content) which have several different numbers in the Level 1 order column. I cannot find a way to resolve this issue. Can you help me to resolve this? Thanks
I am having the same issue
I got something to work. I filtered down to literally just one row of data then since it still said i couldnt sort my level 1s to each other i did more googling and i added this SortLevel1 = 'youtablename here'[Level 1 Order] as an added column into the mapping table. Still only one row of data. I then told it to sort my level 1 by this SortLevel1 and it worked i then went and unfiltered everything in powerquery. I did this for each level 1,2,3,4 for my work file. For some reason level 2 it wouldnt let me still so i will try again maybe later but the rest worked. Hope this helps you!!!
Hi Chris, thank you very much for this detailed video, it's really helpful! I have a quick question regarding the very first measure summing up the balances of the accounts: does your periodic snapshot show the movement on the accounts or the actual balance at the end of each month? Because if the later is the case then i do not quite get why it would make sense to sum up the balances of each month?
Hi Monci,
Thanks for the feedback.
It all depends on on context in which the calculation is taking place.
In this example, I'm showing months in the calculation so it's summing up for the current month, but your right this wouldn't work if you had multiple months in context for the calculation.
Generally in balance sheet this is OK, because your only looking at a single month but you can have more complex calculations. For instance, to figure out the latest month that is in context and return the value for the figure.
Hope that helps
Chris
When I show the cash flow (next video) this shows the movement in balance sheet because we're working out the change from one month to the next to calculate the change in cash using the indirect method.
Hi Chris, can i have the Excel as well as PBIX files shown in this video. Thank you.
Hello Chris, I really liked your video and downloaded your sample file. However, when I pulled up the Financial_Statement_Layouts Data Model filtered on SUM, when I tried to the Sort Level 1 Column (text) by the Level 1 Order Column (numerical) as per your instructions, I am getting a message to say it won't sort and that "There can't be more than 1 value in Level 1 Order for the same value in Level 1". Do I need to change a setting somewhere or is it because you are using a different version of Power BI because it clearly works when you do it. Cheers, Michael
Did you find a fix to this?
Awesome video! My boss is happy with the result, thanks :D
If I want to accumulate instead of just the snapshot, what would the measure be? I tried using calculate, all and max to remove the filter context, and use the current month column, but had no luck :/
Thanks Alejandro,
If your using the accumulation it is more challenging from dax perspective and you need to figure out the point from which your accumulating and then sum from that point forward which involves manipulating the filter context.
I'm not a huge fan of not having complex DAX unless entirely necessary, so I would advise taking the accumulating snapshot and using Power Query (or even better at source) to transform the data. The M code (which sits behind Power query) has its own challenges but it makes your model much more manageable, maintainable and explainable going forward.
Hope that helps
Chris
Hi Chris! Great Class! I have a problem when I try to sort the Balance Sheet Layout to Level 1 Order. The error states that you cannot have more than one Level 1 Order for the same value of Level 1. Do you know how I can correct for this?
Hi Ana.
You cannot sort by more the same text field by more than one sort order. So for instance, the line "Revenue" can only have 1 sort order against it. If you have multiple you'll get this error
I'm struggling with the 'sort by' steps...."There can't be more than one value in 'Level 1 Order' for the same value in 'Level 1'." What is the workaround?
If you filter the balance sheet layout to just the sums in the query editor, apply the sort order and then remove the filter it should work
@@chrisbarber639 amazing tips. thank you Chris!
@@chrisbarber639 Doesn't seem to be working, can you explain further?
Is the input data available for download?
Hi Chris,
This is a wonderful piece of work. Thanks so much for sharing. I never thought this was possible in Power BI. A quick question please, if the periodic snapshot includes subledgers for P&L items and the balance sheet layout table isn't linked to them, how do you do it such that the totals exclude the P&L subledgers please?
Thanks Yomi.
Hopefully I've understood your request correctly in that your snapshot contains both elements of the double entry.
If that's is the case, I would recommend filtering out those ledgers that appear on the p&l from your snapshot using Power query.
In Power BI generally if you've got data in your Fact table (the snapshot) that appears in your dimension table (financial layouts) this is generally considered to be bad practice (a referential integrity issue).
Hope that's useful.
Chris
@@chrisbarber639 Thanks for your prompt response and very helpful advise. As advised, I created a custom column to classify subledgers into P&L and Balance Sheet then filtered out the unwanted numbers using Power Query. Much appreciated.
Hi Chis, great video - are you able to do one covering consolidation of foreign company subsidiaries?
Hi Karl. Generally your ERP system would handle the consolidation and the BI would report on the outcome. For instance, SAP has leading (consolidation) and non leading ledgers (local) and you need to refer to each fact table separately depending on if you want the group or local accounts. This may be an area I do a video on in the future if there's demand.
Thanks
Great sharing! will you share cash flow statement soon :)
looking forward that.
Thanks!! We're live on the 17th May on Cash Flows.
You can sign up for free through the London Business Analytics Group meetup.
@@chrisbarber639 Great!
Can anyone elaborate on how to get PY Amount to calculate correctly?
Hello Chris,
I am not able to download the file with provided link.
can you please help.
github.com/MarkWilcock/lbag-online/tree/master/Balance%20Sheet
@@LondonBusinessAnalyticsGroup Thank you so much.
Hi Very good Thank you. The link is not working
Hi Dino, Link now working. Thanks for the spot.
@@LondonBusinessAnalyticsGroup video can't be played. Why?
@@learnpowerbi - It's all working my end
@@chrisbarber639 working now. Thanks.