Power BI: The Balance Sheet

hello and welcome to this session where we're looking at the balance sheet within power bi today we're going to be looking how to build out this balance sheet within power bi including all the data modeling the dax and the data visualization as always we've ordered the accompanying pbix files in the description below and please leave any comments thank you very much today we're going to cover some finance basics which you're going to need to know in order to build out the balance sheet we're also going to cover some kimball basics which is the data modeling methodology and we're going to cover the three different types of fact tables we're then going to go on to look at the raw data and do some data modeling once we've done that we'll move on to the dax which we use to create our measures and finally we're going to do a bit of data visualization so let's look at our statement of financial position basics the first thing you need to know is the statement of financial position is a snapshot in time and it contains three key elements it contains the assets the liability and the equity the assets is what a company owns the liabilities is what that company owes and the equities is shareholders stake within the organization the statement of financial position is often referred to as the balance sheet because it balances by that we mean that the assets should be equal to the liabilities plus the equity now that we've covered the basics of the balance sheet let's look at some of the nuances the first of these is there isn't a single balance sheet within most large organizations this because many organizations have subsidiaries and associates and subsidiaries themselves can have subsidiaries and associates so for instance if you look at a company like google they've got a parent company called alphabet the ownership of these companies varies therefore if you look at the parent and look at the subsidiary it may only have a proportion of that subsidiary within its balance sheet so for instance an example we have an 80 subsidiary but from that subsidiary's point of view they'll want to see 100 of their balance sheet a byproduct of the parent having many subsidiaries and associates in different countries is that these balance sheets are likely to also have to be prepared in different currencies another key consideration is that you're likely to have multiple accounting standards across different countries so for instance an example given the parent company sits in the u.s and therefore falls under its jurisdiction therefore it has to report its accounting standards according to u.s gap the subsidiaries on the other hand have to report under ifrs therefore we have to prepare accounts under both ifrs and usgap it's important to note that these are not necessarily the same for instance the revenue reported under ifrs is not necessarily the same as the revenue reported on the us gap as they have different rules today we have a somewhat simplified example in that we are only looking at one company one currency and one accounting standard however this approach is scalable and you can build out different balance sheets within the solution now we've covered the basics of finance let's cover the basics of the kimball methodology the kimball methodology is frequently used within data modeling and in it we have two key concepts which we must get to grips with the first of these is fact tables fact tables contain all of the observations or events which we want to record in this case a ledger code and the amount which is next to it so for instance the ledger code 30100 might have a record of 40 pounds against it the dimension tables are used to explain the observations or events recorded within the fact table for instance this will contain our ledger mapping such as that 30100 is related to revenue other key dimensions include those such as date within the kimball methodology there are three key types of fact tables these are the transactional fact tables which are just a record by record of every transaction that goes through the books a periodic snapshot which is a point in time and the amount that we've got so for instance in january 2021 we have 109 but in february 2021 we had 116 and finally we have an accumulating snapshot which is a combination of the last two where the beginning of the year we take a periodic snapshot and then we accumulate all the transactions through it at the end of the year we put in another periodic snapshot and so on and so forth the periodic snapshot and accumulation snapshot are particularly useful when building out balance sheets and we will focus on the periodic snapshot approach today we're now going to move on to look at the data which we're going to be using within our power bi reports we have in column b the level 1 and in column c the level 2 of our balance sheet the level 1 contains things such as non-current assets in the current assets and the level two contains the next level down so for instance within non-current assets we've got property plant equipment and investment for sale and then we have a subtotal in level one for non-current assets we have a number of other useful columns within this as well so for instance level one and level two will be sorted alphabetically by default however we can use columns g and h in order to order those in the way which we like in column k we have the calculation type so some of these will be straight sums whereas others will be calculations of other members within the balance sheet to do that we're going to adjust the filter context using columns m and n i'll talk a bit a bit more about this later on we've also got column o which shows the highlight type this is going to determine the background color and the text color within our balance sheet and in column p we have an operator and column q we have a format string the next table which we're going to be ingesting into our model is the ledger mapping which shows how our balance sheet layout matches to our sub ledgers so in our balance sheet layout we had a primary key which contained a particular line item so for instance 201 was related to plant and this is related to particular subledger in this case 10100 in many cases you can have multiple sub-ledgers attached to a single primary key or element within the balance sheet the penultimate table that we need to look at contains our actual periodic snapshots so for instance in column b we can see each of the individual days and then on row 5 we see each of the ledges so on the first of january 2019 under ledger one zero 100 we can see an amount of 245.8 million we'll actually need to do a little bit of transformation of this when we bring this into the model the final table which we're adjusting to our model contains all of the dates this is common within most models where we have the dates and then we have a number of attributes in this case we've got attributes such as the year the month name in the quarter and again this is quite a common thing to do now that we've looked at our data let's actually start to ingest some of it into power bi to do that let's go over to the excel file and select our data for power bi in order to ingest it that's come up let's select the files that we need in order to bring them through which is the four that i showed you previously these are balance sheet layout date ledger mapping and periodic snapshot and once that's done let's hit the transform data to go over to the query editor within the query editor we have our four files on the left hand side the first thing we're going to do is with the balance sheet layout we're just going to look at the calculations which are sums we'll look at the other ones in detail later this also allows us to enable the source order the next thing that we need to do is we need to look at our periodic snapshot data currently as mentioned when we look through the files we have all the dates down here on the left hand side in all the ledges across the top however in order to lay this out we need to unpivot the data to do that right click on the date and click and pivot other columns here you'll notice it's named the ledger attribute by default so let's just go in and change that to ledger and there we have it the name has changed once we've done that let's go over to close apply to bring this into our power bi now that we've loaded our data into power bi we need to go to the modeling view and we need to organize our data to do that let's put our periodic snapshot which is our fact table in the middle and let's put the other tables surrounding it the first bit we're going to connect to our back table is the date table so let's drag date to our periodic snapshots in order to create that relationship let's also connect up our ledger mapping to our periodic snapshot and we need to make a connection between our balance sheet layout all the way through to our ledger mapping using the primary key don't worry if you don't fully understand this at this point i'll be going through what's exactly going on with these relationships later back on our visualization pane let's go to the matrix visual and add this into our report with this in let's just expand this to the full page and once done let's add in our balance sheet layouts level one and level two if we expand this down in using the expand all down one level in the hierarchy what you'll hopefully see is the sort of this is currently incorrect we need our non-current assets at the top followed by our current assets and so on and so forth this is because it's sorting alphabetically to fix this we need to go over to the table view and amend it within this view which is the second tab down on the left hand side we need to correct all our sort orders so firstly let's select level one and use sort by the level one order once we've done that we then need to do the same for level two level three and level four in this instance level three and four are blank but you might have more levels than this in your balance sheet back on our report pane which shows all of our visuals we can see that it's now sorted correctly we now need to start writing our measures and introducing them into our report you can do this straight away by clicking on new measures however i prefer to introduce a measures table to store all my measures within to do that go to the enter data and just create a measures table which is a blank table you'll see this measure table has now appeared on the right hand side and we can then use this in order to introduce our measures to do so go over to the home tab and select new measure the first measure we'll be creating is going to be the balanced sum which is just going to be the sum of our balanced values from our periodic snapshot table now that we've got our balance sub-measure created let's include it within our report if we select the balance sum and we drag it down to values you'll see that our non-current assets property plant equipment and all the other lines on the balance sheet now have a value against them to understand what's going on here let's go back over to our slides so here we have our balance sheet layout or trimmed down version of it we've got our property plant equipment investment for sale a blank line and our non-current assets we've also got a primary key for each of these individual lines and for the non-current assets we have the from and the two which we use to adjust the filter context we'll get into that later the primary key from this table is linked to our ledger table so we know that primary key 201 relates to subledgers 1 0 100. and then our sub ledgers link to our fact table which contains all of our values if we now follow this through for property we can see that this selects primary key 201 which selects sub ledgers 1 100 which selects all of the values against that subledger totaling to a value of 30 in the example back in our power bi report the sums are being calculated the exact process just described so it's flowing from the balance sheet through to the ledges and finally to the fact table let's now start to bring in the other calculations to do that we need to go over to our transform data in order to bring up our query editor once our query editor is uploaded let's go down to the calculations and let's bring through our calculations and our blanks and click ok and then go back over to close and apply back in our power bi report we can see that no change has happened and to understand why that's the case let's go back over to our slides so within the slides when we've got the non-current assets total selected we are selecting primary key 206 however there's no mapping between a primary key 206 and any sub-ledger instead this is comprised of all of the items above it so some of property planned equipment and investments for sale in order to do this calculation we need to do a few steps and we're going to do it in a couple of stages firstly let's remove the filter context from our ledgers to remove the filter context we need to create a new measure let's call this measure balance subtotal whenever we're changing the filter context we need to use a calculate and what we're going to be calculating is the balance sum which is the previous measure we did except in this case we're going to be doing it for all of our ledger mappings let's now take that calculation and put it onto the values and you can see that for every single row we have the same balance subtotal now showing now that we've removed the filter context we need to reapply a filter context of our own choosing this filter context is going to be based on the from and two columns in the balance sheet layout so we want to generate values of filter context wherever the value is greater than or equal 201 which is in the from column or less than or equal to 205 which is in the two column this will select the appropriate ledges and return the subtotal value let's now do this in power bi desktop with our balance subtotal measure selected the first thing we do is store the variables which are going to store our current from and our current two values to do this we're going to use the selected value to return the latest from and also the appropriate two once we've done that we need to use return which is what we always use after a variable to return our measure we also need to apply a filter which is going to remove the filter context from our ledger mapping and then reapply that filter context using that from in two that we've just defined if we now hide this measure you can now see the balance subtotals are calculated for all the various different lines within our balance sheet the next measure that we're going to add in is just going to count wherever we've got a blank row so let's just add that in and let's include this within our visual so we now have a zero where we want a blank line showing the final calculation that we're going to do is the coalesce calculation this is going to bring together the three measures that we created so far let's call this our total balance and as mentioned we're going to use the coalesce we'll start off with the balance sum if we don't find the balance sum we'll take the balance subtotal and if we don't find the balance subtotal we'll take the blanks if we now drag this onto our visual and remove the previous calculations we now have a combined calculation which we can use we're now going to start to tidy up our reports so let's position this a little bit more centrally and select the month name to go across all of the columns let's also select the current year so that we don't have multiple years within our data and there we have it january through to may for the current year the other thing we're going to do on the formatting pane is we're going to go down and we're going to change the units to thousands and the decimal places to zeros and then i'm just going to group up the various different elements using the plus and minus buttons here you can also do this using the in scope and if you look at the final example in the solution provided you'll be able to see how this is done so here we have something that's starting to resemble an actual balance sheet we still got a fair bit of formatting just to tidy this up so the next thing i'm going to do is go to the style and change it to none which is going to remove the default lines on the report and on the row headers i'm going to remove the plus minus icons to add the highlighting on the correct rows and also to change the text colors we can go over to our transform data to insert another layer of data if we go into enter data and i'm going to paste in the highlight types and the associated hex codes so you may remember in the original balance sheet layout we had a highlight type of one through to six and this provides the hex codes for the background and the text that we can use with this let's call this hex codes and bring it in to our model with the hex codes table let's go to right click and disable the load so it doesn't load into our model instead let's merge it with our balance sheet data using the highlight type to do that go to merge queries and we want to merge the highlight type with the hex code highlight type to return all of the hex codes so firstly this gives us a table and we just need to expand that out when we expand it out we can select the hexagon background and the hexagon text and for each line we now have a hex code background and hex code text which we can use let's go to close and apply and actually bring this through the final stage in getting this to resemble a balance sheet is to use those hex codes within our model so if you go on the formatting page down to background color choose the advanced controls select field value and choose the hex code background that we just created and apply it to the values and totals we now have the hex codes applied to the appropriate lines if we do exactly the same with the font color except this time we choose the hex code for the text we now have something which resembles a balance sheet and we can see that it balances indeed you can see the assets total for may is 295.5 million and it's the same for the total equities and liabilities thank you for watching this session on the balance sheet within power bi all the accompanying files are in the description below and if you have any questions please post them in the comments

Channel: London Business Analytics Group

Views: 7,607

Rating: 5 out of 5

Keywords:

Id: Zqz5PsnlPvA

Length: 20min 25sec (1225 seconds)

Published: Wed Apr 28 2021