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.
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
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.
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!
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 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 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!!!
sqlbi has written an article "Understanding Group By Columns in Power BI". There they explain how to tell the model that 2 different cities with the same name really are different entities. This should work with this problem telling different rows with the same blank value apart. Another approach is to don't have them as blanks naming then Blank01, Blank02 etc and then use the approach with hex colors in this video to set them as same text color as the background efficiently hiding them.
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
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?
Thank you Chirs. I am struggling applying the background color in conditional formatting. For some reason I cannot select my table column "Hex Background" in the conditional formatting as my field value. Conditional window simply does not allow me select. Any Idea why this happens
Thanks for the great efforts, it’s really helpful, yet, I would like to ask what about having more than one company and need to apply filters, I’ve done that but every time I apply the filters, total assets, liabilities, and total equity + liability are always gone as if the filter is being applied to the totals display as well. Thanks for your answer in advance if you can help out
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
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!
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?
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
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?
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.
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
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?
You need to consider what type of fact table (explained at the beginning of the video) you are using to feed your model. 1. In case of transaction fact table for balances you need to aggregate from the beginning of time. In case of periodic snapshot table it is the source data's responsibility to include balance on share capital each period even if it had not moved. In case of the third type (that is not accumulating snapshot as kimball defines it but a hybrid of the other two) you need to aggregate from the beginning of the year (if that is how data is formed.) You still ned source data then to include share capital in the opening balance transaction.
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
Hiii... thanks you. I have some problem, while sum parent and detail child is not same number. Witch is only 0.10 or less than 1 value. Please advice. Thank you
For example Total Asset $40,000.15 Current Asset $30,000.12 Non current asset $10,000.11 It should be $40,000.23 But if i put di card total is $40,000.23 I have already SUM() formula in dax also.
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 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.
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
The description of what an accumulating snapshot table is according to kimball methodology is wrong. It should have one date foreign keys column per prendefined event. e.g. Order date, delivery date, invoice date. Here is just an transaction table described with a first opening balance transaction.
![NISAMANEE - TADA (ท้าดา) [ OFFICIAL MV ]](http://i.ytimg.com/vi/hcQHEPMmJpU/mqdefault.jpg)
Thanks Chris, that is outstanding. So much easier to understand than anything I have seen elsewhere. I look forward to implementing it
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
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?
Super understanding of Power BI. Sharing is appreciated.
A tremendous piece of work, Chris. Much appreciated for posting it.
Thanks Rob
Fabulous! Loads of suggestions in just 20 minutes. Now let's see if I can reproduce it in 20 minutes :)
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.
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
What an awesome presentation!
Thanks Brian
This video is so well put together and succinct. Thank you for sharing this.
Thanks Mike
this is amazing, truly awesome
Fantastic explanation ! good job
Thanks Adrian
Great presentation!
Thanks Joanne
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!!!
sqlbi has written an article "Understanding Group By Columns in Power BI". There they explain how to tell the model that 2 different cities with the same name really are different entities. This should work with this problem telling different rows with the same blank value apart. Another approach is to don't have them as blanks naming then Blank01, Blank02 etc and then use the approach with hex colors in this video to set them as same text color as the background efficiently hiding them.
very well explained, thanks.
Thank you Kamran
Thanks for sharing this. Does this process support drill through to the transactions?
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
This is so interesting Chris
Thank you Meryem. Hope you find it useful
Thanks for your sharing.
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?
Thank you Chirs. I am struggling applying the background color in conditional formatting. For some reason I cannot select my table column "Hex Background" in the conditional formatting as my field value. Conditional window simply does not allow me select. Any Idea why this happens
Thank you for the video. It’s really helpful and simple to understand ❤
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 for the great efforts, it’s really helpful, yet, I would like to ask what about having more than one company and need to apply filters, I’ve done that but every time I apply the filters, total assets, liabilities, and total equity + liability are always gone as if the filter is being applied to the totals display as well. Thanks for your answer in advance if you can help out
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?
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!
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?
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.
coalesce with three arguments is great
Thanks!
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?
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!
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.
Hi Chris, can i have the Excel as well as PBIX files shown in this video. Thank you.
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
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?
You need to consider what type of fact table (explained at the beginning of the video) you are using to feed your model. 1. In case of transaction fact table for balances you need to aggregate from the beginning of time. In case of periodic snapshot table it is the source data's responsibility to include balance on share capital each period even if it had not moved. In case of the third type (that is not accumulating snapshot as kimball defines it but a hybrid of the other two) you need to aggregate from the beginning of the year (if that is how data is formed.) You still ned source data then to include share capital in the opening balance transaction.
Thanks for sharing, helped me alot! :)
No worries Youri - glad it was helpful
Manm your an angel! Thank you so much!
Thanks Murilo
Wow! This is amaizing
Thanks Abdan!!
Hi Chris, thank you very much
Thank you so much, well explained..
Is the input data available for download?
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
Hiii... thanks you.
I have some problem, while sum parent and detail child is not same number.
Witch is only 0.10 or less than 1 value.
Please advice. Thank you
For example
Total Asset $40,000.15
Current Asset $30,000.12
Non current asset $10,000.11
It should be $40,000.23
But if i put di card total is $40,000.23
I have already SUM() formula in dax also.
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 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 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.
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
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.
The description of what an accumulating snapshot table is according to kimball methodology is wrong. It should have one date foreign keys column per prendefined event. e.g. Order date, delivery date, invoice date. Here is just an transaction table described with a first opening balance transaction.
Can anyone elaborate on how to get PY Amount to calculate correctly?
Thanks
Thank you! Great tutorial.
Thank you Rafael