1. Liquidity dashboard
We are going to develop a dynamic dashboard in Power BI to show performance ratios.
First we are going to create a dashboard for our liquidity ratios.
To do this we will start by inserting a matrix table from our visualization pane.
You will notice that we have a new table called "Liquidity ratios" in our Data pane.
Go to the Data view and select the Liquidity ratio table. You will see liquidity ratios in the table, inclusive of the current ratio, acid test and inventory turnover.
Now, we are going to review how to calculate these three liquidity ratios. We will then create a measure called ‘Actuals selected ratios balance sheet’ to add the current ratio nicely to our matrix.
First we will create a measure called ‘Current Ratio’ under the "Balance Sheet Model Measures".
We know that current ratio is current assets divided by current liabilities, so we create this measure using measures given to us; "Actual Total Current Assets" and "Actual Total Current Liabilities". Your equation should be "Actual Total Current Assets" divided by "Actual Total Current Liabilities"
Next we create a measure call 'Actuals Selected Ratios Balance Sheet’. We create this measure in order to create nice visualization of our liquidity ratios to add to our dashboard. Create this measure under "Balance Sheet Model Measures". To create the measure we use the SELECTEDVALUE DAX commands.
This command returns the value of the column reference passed as first argument if it is the only value available in the filter context, otherwise it returns blank or the default value passed as the second argument.
Then we will use the SWITCH DAX command to select only the current ratio values. Switch is a logical concept that evaluates an expression against a list of values and returns one of the multiple possible result expressions.
The result is this equation,'Actuals Selected Ratios Balance Sheet’:
1. VAR selectedratios = SELECTEDVALUE('Liquidity Ratios'[Ratio])
Return
SWITCH(
true(),
selectedratios = "Current Ratio",[Current Ratio]
)
Now to add to our visualization model we add "Actuals Selected Ratio Balance Sheet" from our model measures to Values in our matrix. Then we add ‘Year’ under columns from our date table ‘Date Balance Sheet’.
Now you can see that we have our current ratio dashboard.
We then turn off the row and column totals.
We add a title, ’Liquidity Ratios’ and update the formatting to match our income statement and balance sheet.
2. Let's practice!