Getting to know the balance sheet
1. Getting to know the balance sheet
In this screencast, we will explore the data and create measures to analyze our balance sheet. We will then use visualizations to understand the Data better. Your CEO is excited to have you on board as a new analyst and has asked you to create a dynamic dashboard for Larry’s Automotive. First, let’s take a look at our data. We see in the data pane that we have five tables loaded. We have one fact table called Actuals and three dimension tables, first we have the Balance Sheet Layout, second, the Date, and third, Ledger Mapping. Then we have a table called "Model Measures", this will hold all our measures. We can see here in our model view how these tables are connected to give us our balance sheet. Now let's look at our balance sheet. Here we see all of the data summed from 2019 to 2021. Analyzing the data would be most effective if we could see the balance sheet for each year. In order to do this, we will create a slicer that filters the data annually. Go to the visualization pane, click the slicer button, and click “Year” under the Date table. We want to be able to easily see the data for specific years. We select the formatting pane under visualization, and under the “slicer settings” drop-down, we click “Tile” under “options”. Now we can filter the data to show different information for each year. You can see that when you click each year only that years data appears. You can see the total data for 2019 to 2021 when none of the buttons are selected. Now that we have the option of filtering the balance sheet, let’s look at the data for specific line items. Let’s first look at the line item of Property. This is under Long-Term Assets. Long-Term Assets are assets and property owned by a business that are not easily converted. Now we are going to create measures for the total property. You will notice that we have a separate table for all of our measures. So we put in Total Property, Calculate, and then we use “sum” to add a filter portion of our data to the values. We then use the Filter Command to look in the Ledger row to find all the Property Transactions. The filter command allows us to look in the table "Actuals", then look for the column "Balance Sheet Line Items" and then find the values for "Property". Your final measure should look like this: 'Total Property = CALCULATE(sum(Actuals[Value]), FILTER(Actuals,Actuals[Balance Sheet Line Items] = "Property"))''. Click enter and you will see it appear in our data pane. Now we add a card visualization and select “2021” in our slicer and select the measure Total Property, we are able to see the total property for 2021 which is 2.5 Billion. We change the decimal place to 1 by selecting Formatting, Visual, Callout value and changing the Value Decimal Places” to “1” Now you will visualize this data over several years. It’s your turn.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.