Save content
Have you found this content useful? Use the button above to save it to your profile.
Data visualisation| AccountingWEB | ABC of Power BI: P is for percentage of sales
istock_data-viualisaton_primeimages

ABC of Power BI: ‘P’ is for percentage of sales

by

Continuing his Power BI series, Hugh Johnson explores ways to scrutinise an income statement and unravel the drivers behind changes in profitability.

10th Jan 2024
Save content
Have you found this content useful? Use the button above to save it to your profile.

Tracking how the various lines of your income statement and how they are moving in relation to sales is one of the simplest and most useful things that you can do to analyse your company’s performance. The good news is that it is straightforward to set up in Power BI. 

Consider the example below: 

An image of an income statement
Hugh Johnson

This chart shows the income statement for the selected period with the actual expressed numerically, and then compares the actual and previous periods as a percentage of sales (% of sales).  

The "variance ppt" column shows the variance in the "% of sales" from one year to the next. Simply scanning this column immediately shows the drivers behind any changes in profitability in relation to sales. 

You’ll quickly see the overall profitability has improved dramatically, driven mostly by better margins because of improved purchasing driving better gross margins, but also with good overall overhead cost control, particularly with gross wages. 

How to do the percentage of sales calculation in Power BI 

Like all things Power BI, there is no single, correct way to do this. You will always, however, need to divide whatever value you calculate for a given row of the income statement by the total sales value for the same period.  

This will inevitably require that you use the CALCULATE function to manipulate the Filter Context. The following steps are how I typically do this. 

Step 1: Create a base measure for “all sales”

A starting point could be to create a 'Base Measure' that calculates the total sales value (ignoring any date filters that you may apply later). Such a measure might look like this: 

A step-by-step example: base measure
Hugh Johnson

The measure is working with a model like the one described here and in the diagram below. 

A step-by-step example: Financial modelling
Hugh Johnson

This calculation is summing the values of a ‘Journals’ table that contains all actual and budgeted (pseudo) postings that is filtered by Journals[Source] = “actual” to remove the budget journals.  

In step 7, ALL ( Accounts ) removes any filters from the “Accounts” table that may be coming from the rows of the income statement, and therefore enables the following step 8 to apply a new filter Accounts[AccountClass] = “income”.  

In the model, Accounts[AccountClass] is a column with five possible values: income, expense, asset, liability and equity. It is primarily used to control the display of the income statement values, for example, to show costs as positive or negative amounts but doubles up here to identify the sales accounts.

A further refinement would be to have a specific column in the “Accounts" table to identify the sales accounts. This can be useful if you want to exclude accounts like “Income from the Sale of Assets”, "Interest Income", or "Exceptional Income" from the calculation. In which case, the base calculation might look like this: 

Step by step example: BasePL_sales
Hugh Johnson

Note in this example, there is also the additional line 7 to filter out year-end transfers to the balance sheet. This is not relevant to the discussion here. 

Step 2: Add a disconnected “switch” to toggle between “Actual” and “% of Sales” 

If your intention is to be able to toggle between “Actual” and “% of sales”, then you can implement this through a disconnected table. This is a table that can be used in a slicer to capture the user’s intentions but is not connected to the rest of the model through any relationships.  

In this example, it is a simple three-column, two-row table SwitchCalculation as shown below. 

Step-by-step examples: Switch calculation
Hugh Johnson
  • SwitchCalculation[Method] displays the label that the user sees in the slicer 
  • SwitchCalculation[MethodRef] is the value used in DAX measures 
  • SwitchCalculation[MethodID] provides the sort order for SwitchCalculation[Method] 

Step 3: Apply the selection between “Actual” and “% of sales” 

There are two common scenarios that I use to apply this selection. One is based on the column of a matrix visualisation, like I have here, where the “% of sales” column has an attribute that indicates this is a percentage of sales calculation. 

Step by step example: Income statement header
Hugh Johnson

The other is where for the "Prev. Year" column (using the disconnected slicer in Step two), I switch to a % of sales calculation, so I get this: 

Step by step example: Income statement header picture of sales
Hugh Johnson

I typically use a variable called “CalculationMethod” that is either “absolute” or “percent”, depending on the combination of the columns of the matrix and the disconnected switch in step two above. You define this based on how you want the report to behave. 

The calculation of the P&L amount is then like this: 

Actual calculation
Hugh Johnson

In this calculation, the MaxRelativeDay and MinRelativeDay variables define the date range, based in slicer selections. 

[BasePL_AllSales] is a measure that returns the “all sales” calculation, ignoring any dates filters. Essentially it is the sum of all journal transactions on the sales accounts, multiplied by minus 1. 

[BasePL] is a measure that returns the sum of all journal transactions (multiplied by minus 1), ignoring any date filters, for the account codes that correspond to each given row of the Income Statement. 

“PercentageMultiplier” is a variable that returns “1” for an “absolute” calculation or 1/(Total Sales) for a ”percentage” calculation. 

The final calculation is very simple, and is shown in rows 51 to 56, taking the [BasePL] amount, multiplying by the “PercentageMultiplier” and applying the relevant date filters. 

Summary 

  • % of Sales is a very valuable addition to any income statement presentation. 
  • It is actually very simple to do in Power BI.  
  • They key is to make sure that in your data model you can easily determine which accounts contribute to the total sales figure. 
  • After that, the calculations are very simple. What you need to focus on is how you want to present the information and how the user can interact with the report. 

Useful links 

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.