Income Statement Analysis: Part 4 of 5: Add KPI Cards and Waterfall

[rsaprano]

________________

In this 5 part series, we walk through the steps required to build out the first page of the Income Statement Report here. 

The topics covered in these walkthroughs include:

- Part 1: Connecting to Data (Folder Containing CSVs), Importing into Power BI

- Part 2: Creating the base measures and an Income Statement Matrix visual

- Part 3: Adding advanced measures e.g. based on the period selection (covering use of disconnected pa...

- Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and mea...

- Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bo...

____________

In the previous steps we built a pivot table-style Income Statement Matrix, created dynamic calculations for MTD/QTD/YTD and applied conditional formatting on the variance columns based on a measure.

One of the key aspects of storytelling with data, however, is to make it easy for the reader to see performance at a glance and understand what is driving that performance. For this we will a set of ‘pimped up’ card visuals showing the key Income statement numbers, a comparison to the value last year and a visual, conditionally formatted arrow icon for each indicating whether the number has increased or decreased (and whether that increase is good or bad):

To understand the drivers behind performance we will also build a waterfall chart that will show the Income Statement line value (defaulting to Profit After Tax but responding to when a row on the IS is selected) at the start of the month/quarter/year, the change in the value for each organisational unit and the corresponding value at the end of the month/quarter/year:

This will allow us to see which organisational units have contributed to the overall increase or decrease in the line item.

1         Create the measures for the KPI Cards.

We firstly need to create all the main (Actuals) measures that we want to show in the card visuals – Revenue, Gross Profit, Profit Before Tax and Profit After Tax.

These can all be achieved using the CALCULATE function, filtering for the Header item in the DimHeaders table which, through the relationships we set up in Step 2, will filter for all the corresponding GL Accounts which are associated with that header.

I write these filter-based measures in a certain way – creating a variable for the filter condition using CALCULATETABLE and then passing that variable into the CALCULATE function (See here for a how-to topic on this).

The measures for the key numbers in each of the KPI cards can therefore be written as:

Revenue Actuals = 
VAR FSLineFilter = CALCULATETABLE(DimHeaders,DimHders[Header]="Revenue")
RETURN
CALCULATE([Actuals By Period],FSLineFilter) + 0

COGS Actuals = 
VAR FSLineFilter = CALCULATETABLE(DimHeaders,DimHeaders[Header]="Cost of Sales")
RETURN
CALCULATE([Actuals By Period],FSLineFilter)

Gross Profit Actuals = [Revenue Actuals] + [COGS Actuals]

Opex Actuals = 
VAR FSLineFilter = CALCULATETABLE(DimHeaders,DimHeaders[Header]="Operating Expenses")
RETURN
CALCULATE([Actuals By Period],FSLineFilter)

Other Income and Expense Actuals = 
VAR FSLineFilter = CALCULATETABLE(DimHeaders,DimHeaders[Header]="Other Income and Expense")
RETURN
CALCULATE([Actuals By Period],FSLineFilter)

Tax Actuals = 
VAR FSLineFilter = CALCULATETABLE(DimHeaders,DimHeaders[Header]="Taxes")
RETURN
CALCULATE([Actuals By Period],FSLineFilter)

Profit After Tax Actuals = [Revenue Actuals] + [COGS Actuals] + [Opex Actuals] + [Other Income and Expense Actuals] + [Tax Actuals]

Profit Before Tax Actuals = [Profit After Tax Actuals] - [Tax Actuals]

2         Create Dynamic measures to show variations of these as required

Because we used the Actuals By Period measure as a base for these 8 measures above, it will automatically respond to the MTD/QTD/YTD slicer selection. Rather than create another 8 measures for the PY value (based on the Actuals PY Measure), we can  limit the number of measures we need to create by following a similar pattern that we did for the period selection – we create a parameter table and use SELECTEDVALUE in our measures to make it dynamic. The difference is that this value won’t be selected by users in a slicer but rather we will set the single value as a visual level filter for each visual.

We start with creating a Measure Table parameter table using ‘Enter Data’ and create the following table:

We then need to create the dynamic measure for CY Actuals depending on the specific value set for the table:

Measure Value CY = 
VAR SelectedMeasure = SELECTEDVALUE('Measure Selection'[Measure],"Revenue")
RETURN
SWITCH(SelectedMeasure,
        "Revenue",[Revenue Actuals],
        "Gross Profit",[Gross Profit Actuals],
        "Profit Before Tax",[Profit Before Tax Actuals],
        "Profit After Tax",[Profit After Tax Actuals],
        [Revenue Actuals]
    )

And then the corresponding value for PY:

Measure Value PY = CALCULATE([Measure Value CY],SAMEPERIODLASTYEAR(DimDates[Date]))

And the variance from CY to PY:

Measure Value Delta = [Measure Value CY] - [Measure Value PY]

4         Create the KPI Card Visuals

Next we will create 4 KPI Card visuals (Revenue, Gross Profit, Profit Before Tax, Profit After Tax):

• Each KPI Card should have the measure ‘Measure Value CY’ in the Fields bucket
• Each one should have a (locked) visual level filter applied for the field Measure Selection[Measure] – the value for the filter is the only thing that will vary between them
• Format the KPI cards with a white background (0% Transparency), no category label, a title with the name of the measure formatted as 11px white font/light grey background and centre aligned:

The KPI Cards should now look like:

4         Add the Measure Delta arrows

Next, we will create the up/down arrows which are to appear by the side of the number indicating whether the value has gone up or down compared to the prior year.

We are able to create the arrows as a measure using the UNICHAR function which returns symbols. This (as opposed to using arrow images) allows for it to be dynamic, responding to slicer/cross-filter selections and changing direction depending on whether the value has increased or decreased.

The UNICHAR function takes a code – different symbols have different codes and an internet search will the various codes for a large range of symbols. The ones used here are the code 9650 for the up arrow and 9660 for the down arrow.

We previously created the Measure Value Delta measure to calculate the value for the variance to PY. So, using this and returning a corresponding up/down arrow would give us a measure like:

Measure Delta Arrow = 
VAR Delta = [Measure Value Delta]
VAR DeltaIsPositive = Delta > 0
RETURN
IF(DeltaIsPositive,UNICHAR(9650),UNICHAR(9660))

Use this measure in a card visual and place it next to the number on each measure KPI card, applying the same visual level filter to each arrow card as the one with the main number (i.e. Revenue, Gross Profit, Profit Before Tax, Profit After Tax):

We then need to apply conditional formatting to the arrows so that the up arrows are shown in green and down arrows in red (All these measures are Revenue/Profit based so an increase is good; if we had a cost-based measure in here instead we would reverse the conditional formatting rule for that arrow).

To do this we click on the three dots in the data label section of the formatting section of the arrow card, select rules and apply the following rules based on the Measure Value Delta:

The KPI Cards should now look like:

5         Create the PY/Delta measures for the KPI Cards

Underneath each KPI Card we also want to show the PY value and the delta to PY e.g.:

• Create two text boxes for each measure, one with the text ‘PY:’ and one with the text ‘Delta:’. Format these with 10.5pt font size
• Create two Card visuals for each measure, one with the ‘Measure Value PY’ measure and one with ‘Measure Value Delta’ measure. Format these with no category label and 12pt font size for the data label
• Fit the text boxes and card visuals underneath the KPI cards as shown in the screenshot above
• Apply the same visual level filter to the card visuals as the main KPI cards and the arrows, based on the field Measure Selection[Measure]
• Apply the same conditional formatting to the data label for the Delta card visual as was applied to the arrow (based on Rules against the measure ‘Measure Value Delta’)

6         Create Axis/Measure for Waterfall Chart

Waterfall charts are a very powerful visual for showing the breakdown from the start of a period to the end of a period by a particular dimension (e.g. by organisation unit). In this case, we want to show the value of the Income Statement Line (e.g. Profit After Tax) at the start of the month/quarter/year, at the end of the period and the organisations that have contributed to the increase/decrease during the period.

The measure we need to use in the waterfall chart is the Actuals By Period – by default this shows the total actuals which is the same as Profit After Tax. Our initial instinct when creating a waterfall chart might be therefore to put this measure into the Y axis and Parent Organisation into the category, which will give us a waterfall chart as follows:

Whilst this is useful for seeing the breakdown of Profit by Parent Organisation unit, it doesn’t show us what our profit was in the prior month/this month and then used each organisation’s profit as a breakdown category to explain the difference.

So, to create a waterfall chart in the style we want for this kind of financial analysis, we will need to define our own axis and create a dynamic measure to show the points. We can make use again of a parameter table (containing our axis values) and again use SELECTEDVALUE in our measure; this time the actual selected value is determined neither by a user selection nor by a visual level filter, but rather by the axis of the chart – i.e. the various bars on the chart will calculate different values depending on the axis value which they relate to.

Our axis values will vary depending on whether MTD/QTD/YTD is selected – e.g. if MTD is selected it will show ‘Start of Month’, ‘End of Month’ and monthly values; for QTD it will show ‘Start of Quarter’ and ‘QTD’

So, we create a manual table using Enter Data as follows, calling the new table ‘PeriodWaterfallSelections’:

Period	Waterfall Selections	Order
MTD	Start of Month	1
MTD	End of Month	2
QTD	Start of Quarter	3
QTD	QTD	4
YTD	Start of Year	5
YTD	YTD	6

These rows represent the X-axis labels for our waterfall chart depending on whether MTD, QTD or YTD is selected as the period for analysis.

Although this is a manual parameter table, it won’t be disconnected – we need to create a relationship between the period selection table and this one on the Period Field:

The manual table we created above also has an order column which we want to use as a sort column – sort the Waterfall Selections field by the order column in the PeriodWaterfallSelections table:

7         Create waterfall measure

We want a measure which will show the MTD, QTD or YTD Actuals on the waterfall chart (and the values at the start/end of the relevant period) depending on the period value selected.

Total Actuals for Waterfall = 
VAR SelectedWaterfallPoint = SELECTEDVALUE(PeriodWaterfallSelections[Waterfall Selections])
RETURN
SWITCH(SelectedWaterfallPoint,
        "Start of Month",
            CALCULATE([Actuals By Period],DATEADD(DimDates[Date],-1,MONTH)),
        "End of Month",
            [Actuals By Period],
        "Start of Quarter",
            CALCULATE([Actuals By Period],STARTOFQUARTER(DimDates[Date])),
        "QTD",
            CALCULATE([Actuals By Period],DATESQTD(DimDates[Date])),
        "Start of Year",
            CALCULATE([Actuals By Period],STARTOFYEAR(DimDates[Date])),
        "YTD",
            CALCULATE([Actuals By Period],DATESYTD(DimDates[Date])),
            BLANK()
)

This measure uses the functions STARTOFQUARTER and STARTOFYEAR to return the values for those periods. As we only have monthly data, the corresponding value for the start of the month is taken as the prior month value.

8         Create Waterfall chart

Go through the following steps to create the waterfall chart:

• Add a waterfall visual to the canvas
• In the Category bucket, add the field PeriodWaterfallSelections[Waterfall Selections]
• In the Y Axis bucket, add the measure ‘Total Actuals for Waterfall’
• In the breakdown bucket, add the field DimOrganizations[Parent Organization]
• Sort the waterfall chart by Waterfall selections in ascending order
• Format the waterfall chart as follows:
• White background with 0% transparency
• Add Data Labels (Display thousands)
• Change the sentiment colours to show totals in dark blue
• Format the title as 11pt, white font with light grey background with the text “Organisation Actuals Waterfall for Month/Quarter/Year”
• Re-size/arrange the waterfall chart so the report page now looks like the screenshot below

These steps are shown in the following video:

The completed page should now look like:

Download the PBIX attached to see the steps completed

Step 4 -Add KPI Cards and Waterfall Chart.pbix