Income Statement Analysis: Part 2 of 5: Create Simple Matrix view with Actuals

[rsaprano]

_______________________________

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 part 1 of this blog series, we connected to the folder containing the Monthly CSVs and the Reference Data Excel file and brought the data into Power BI. In this section we will create an initial set of calculations and show the results in a pivot-table style Matrix visual.

1         View/Manage Relationships

In Power BI Desktop, navigate to the relationships view window (the third one in the left panel). Re-arrange the tables so that they resemble the layout shown here:

Arranging the tables in this way is not required though reflects visually what is known as a star schema.- The transaction table (Finance Data) contains keys to link to all the Reference data tables.

The relationships are shown by the lines between the tables and double clicking on the line will show the field from each table which is used for the relationship - e.g. Account on the DimAccount table links to AccountKey on the Finance Data. The 1 and * on each line indicates the cardinality of the data in the field: the reference data tables contain unique rows for the keys (e.g. the DimAccount table contains one row per GL account) whereas the transaction table potentially contains repeated values for each key as e.g. there are likely multiple transactions which relate to any given account.

The arrow on the relationship line indicates the flow of data – you use the reference data fields on an axis of a chart (or on rows/columns of a table or matrix) and this works as a filter for that data point, filtering transactions for those reference data fields. It is possible to change the flow of data to filter in both directions though doing so risks creating model ambiguity.

Click on ‘Manage Relationships’ under the ‘Modelling’ tab and ensure that all the following relationships are being shown:

Power BI has autodetected all these relationships because the fields have the same name in the transaction and reference data tables, and there is a 1:many relationship between them. This is the default behaviour for a file though sometimes creates relationships which shouldn’t exist! The behaviour can be turned off by under the options dialog in Power BI:

One table which has not any relationships created from it is DimHeaders. On closer inspection, it does relate to the DimAccounts table – the Header field in DImHeaders should be related to the field ‘HeaderAssignment’ in DimAccounts. This relationship was not automatically created because the fields have different names, though it is easy for us to create it manually ourselves.

Click on New in the manage relationship dialog to create a new relationship and create the following relationship by selecting the columns from each table: DimHeaders[Header] -> DimAccounts[HeaderAssignment]:

2         Create a calculated Column/Measure

There are two types of calculations in Power BI – Calculated columns within a table (which calculate for each row of that table much in the same way as Excel formulas), and Measures which work by aggregating column values. Measures are like pivot table formulas – they work as an aggregation  e.g:

 Total Amount = SUM(FinanceData[AmountUSD])

Like in a Pivot table, this sum measure can then be shown for any combination of fields from the reference data tables – e.g. for any combination of Account Header (Revenue/Expenses), Date, Account Key, Organisation, and Scenario. Plotting this measure in the values section of a table with Account Header on Rows and Scenario on columns will return:

Which is already quite a useful visual – it shows us our Top-Level Income Statement Line amounts for Actual and Budget. However, the Total does not represent Net Profit because both Revenue and Expenses are shown as positive values; for them to be summed together effectively we need to represent the expenses as a negative amount, i.e. multiply it by -1.

This multiplier is shown against each GL Account in the DimAccounts table (it is important to have it at this level rather than the Account header level because both Revenue and Expenses can have positive and negative GL’s underneath them):

To bring this into our measure we can’t just refer to the Sign column. The Measure

 Total Amount Wrong = SUM(FinanceData[AmountUSD])*DimAccounts[Sign] 

won’t work because it contains a naked reference to the Sign column; it needs to be contained in an aggregation function such as Sum or Average. In this case, however, aggregating the sign across multiple GLs makes no sense. Instead we need to perform the calculation on a row by row basis and sum the final result. We can do this either using an iterator function (SUMX) or by use of a calculated column.

Using SUMX the measure:

Total Amount With Sign Correct = SUMX(FinanceData,FinanceData[AmountUSD]*RELATED(DimAccounts[Sign])) 

would work. As a calculated column we could add a column:

 Amount With Sign = FinanceData[AmountUSD]*RELATED(DimAccounts[Sign])

And then have a measure that sums values in this new column:

 Total Amount With Sign = SUM(FinanceData[Amount With Sign]) 

Of course, the value returned by this measure doesn’t make any sense by itself – it sums all types of values (e.g. budget and actual) together. For a meaningful number, we will need to calculate Actuals and Budget which we will do in the next step.

3         Create Actuals and PY Measures

One of the main benefits of Power BI is the ability to create measures with filter logic embedded in the calculation which allows us to create metrics that are more complex than simple aggregations. An example is where we want to show a growth value since last year. In Excel we would only be able to calculate this row by row on a transactional level and perhaps average the result, though with Power BI (or PowerPivot in Excel) we can create base measures for Actuals and PY Actuals and then a third measure which calculates the growth percentage; we can then show just the growth percentage, e.g. by customer, without having to show all the underlying measures.

The DAX function we use to create a measure with a filter condition specified within it is CALCULATE. For our base actuals measure we would write this as:

 Actuals = CALCULATE([Total Amount With Sign],DimScenario[Scenario]="Actual") 

This takes our Total Amount With Sign measure and evaluates it in the context where the value in the (related) Scenario table is set to ‘Actual’. This makes use of the relationship between the DimScenario and FinanceData tables – the filter applied on DimScenario (for the value of Actual) passes down via the ScenarioKey field into FinanceData.

We can then use this Actuals Measure as a base for other measures:

 Actuals PY = CALCULATE([Actuals],SAMEPERIODLASTYEAR(DimDates[Date])) 

Which takes the Actuals measure and evaluates it for the same period last year (Relative to whatever time period is on/applied to the axis of the chart we are looking at), and:

 Actuals Variance to PY = [Actuals] - [Actuals PY] 

 Actuals Variance to PY = [Actuals] - [Actuals PY] 

Actuals Annual Growth % = DIVIDE([Actuals Variance to PY],[Actuals PY])

We also need to format the Measures Actuals, Actuals PY, and Actuals Variance. This can be done by navigating to the relationships view window (the third one in the left panel), selecting “Actuals”, choose format “Custom” and enter in the Custom Format: "£"#,0;("£"#,0);"£"#,0.  (Include all the speech marks). This will show numbers with a pound sign, a comma separator and negative numbers in brackets .

Repeat for “Actuals PY” & “Actuals Variance”.

4         Create simple matrix

•         Start by dragging the actuals measure onto the canvas and choose to represent it as a card visual. It should show     19.93m.

• Apply the required sort order by selecting a field from the fields list and then selecting Column Tools -> Sort By Column (Shown in video under the bullet ‘sort’ below). 

             -DimHeaders[Header] Sorted By Order

             -DimDates[Month] sorted by MonthNum

• Mark the DimDates table as a calendar table by selecting Table Tools (With the table selected) -> Mark As Date Table and selecting the DimDates[Date] field:

• Change the visual type of theA card to a matrix and drag the following fields into the Rows bucket:

           - DimHeaders[Header]

           - DimAccounts[SubHeader]

           - DimAccounts[SubHeader2]

• Sort: Click on the 3 dots in the header visual, select Sort By and select Header, ensuring it is sorted in ascending order (Bringing Revenue first and Taxes last):

• Add the Actuals PY, Actuals Variance to PY and Annual Growth % Measures to the Values bucket.

5        Apply formatting to Matrix

Apply the following formatting changes in the matrix:

• Under Grid, set the row padding to 13
• Under Column Headers, set the Text size to 11
• Under Row Headers, set the Text size to 11 and set the +/- icons to On
• Under Values, set the font size to 11pt
• Under Subtotal, change the background colour to a light blue and set the Row subtotals label to ‘Profit After Tax’
• Under Title, set the title to ‘Income Statement’. Set the font colour to white, the background colour to dark grey and the Text size to 11pt

The matrix visual should now look something like:

6         Add Slicers

• Add two new slicers to the page, one for DimDates[Year] and one for DimDates[Month]

• Change both slicers to a dropdown type (clicking on the arrow next to the eraser in the slicer header) and under the formatting tab of each, Navigate to Selection Controls and turn single select on for both slicers

• Change the slicer values to May 2008

The PBIX should now look like that in the attached

Step 2 - Create Simple Matrix.pbix