Director Accounting Insights Ltd.
Share this content
A concept of data visualisation
istock_data-visualisation_ConceptCafe

ABC of Power BI: G is for gross profit (and other summary totals and ratios)

by

When you start to use Power BI for financial reports, perhaps the first ‘difficult’ thing you will come across is how to calculate gross profit. Such a trivial calculation in Excel requires a bit more thought in Power BI. Hugh Johnson explain why, and how to go about it. 

24th Sep 2021
Director Accounting Insights Ltd.
Share this content

Consider the simple Profit & Loss statement below: 

How to calculate gross profit

The P&L statement is on two levels: a main grouping, such as sales, purchases, gross profit etc, and GL account categories such as product sales and gross wages. 

Except for gross profit and EBITDA, each line represents a simple aggregation of the values at the lower level within the matrix. In other words, if it were not for the gross profit or EBITDA rows, this P&L statement in Power BI would be very similar to a simple Pivot Table in Excel. 

The gross profit line gives us a problem, though. Although it is mathematically the sum of seven categories on the level below (product sales, other sales, purchases, purchase costs, stock purchases, labour, commissions, sales promotions and miscellaneous expenses), we do not want to repeat them all again underneath the gross profit subtotal.   

It can also a problem because it may not be present in your GL ‘Accounts’ table.  If gross profit is not a row in one of your tables then will not be able to display it as a row in a P&L statement. Before we look at how to solve this, let’s first look at the simplest of data models for a P&L statement. 

A very simple data model for a P&L statement 

A simple model to support a P&L Statement where there are no irregular subtotals might look like this:

Simple P&L data

Dates is the Power BI Date table, Transactions is a table with all of the P&L transactions and Accounts is a table of your GL accounts where each row in ‘Accounts’ represents a posting account of the General Ledger. 
 
A measure to calculate the P&L might look like this: 

PLActual :=  
   CALCULATE (  
      SUM ( Transactions[Amount] ), 
      ALL ( Dates ), 
      Dates[Date] <= MaxDate, 
      Dates[Date >= MinDate 

 
Where MaxDate and MinDate are variables that define the selected date range. 

As it stands, we cannot include a gross profit line in the P&L statement because gross profit is not in the Accounts table, and there are not any rows in Transactions that reference “gross profit”.   

The simplest way around this is to display the gross profit value separately, perhaps in a card visualisation. I could use the same [PLActual] measure and simply apply visual-level filters to include only the accounts that make up gross profit. The result might look like this:

Gross profit calculated

If we want a gross profit row in the P&L statement, then we need to include a gross profit row in the table that presents the rows of the P&L statement. Our options are: 

  1. Append a new gross profit row to Accounts, in which case we need to modify the [PLActual] measure to perform a different calculation for this gross profit row; or 
  2. Create a new ChartPL table that defines the layout of the P&L statement as we want it, in which case we somehow need to map the rows of ChartPL to Accounts and therefore Transactions. 

At first glance, you might expect the first option to be better since it is flatter and involves fewer tables and relationships. It is problematic though; the DAX code rapidly becomes quite complex as we add more irregular subtotals to the P&L statement, and it is difficult to enable the famous Power BI interactivity between the new gross profit line and other visuals on the page, tooltips and drillthrough pages. For these reasons, I would default to go with option two and create a new ‘ChartPL’ table.   

A separate ChartPL table to display your P&L Statement 

I first saw this idea in a 2016 Blog by The BI Accountant. Using a separate table to control the display of your P&L statement makes it easier to get the layout that you want, including any blank rows and irregular subtotals like gross profit.   

If we do this, though, we also need an additional bridging table to define how the rows of the P&L statement map to the GL accounts. 

The image below shows the new tables, with ChartPL defining the layout of the P&L statement and BridgeChartPLAccounts defining how each row in ChartPL maps to the rows in Accounts.

Bridging table

In the example, the primary key (unique value for each row) of the Accounts table is a column called Accounts[Group-Category-AccountKey] and the primary key for the ChartPL table is a column called ChartPL[ChartPNLLineID]. 

The mapping table ‘BridgeChartPLAccounts’ only needs these two columns and maintains the mapping between Accounts and ChartPL. 

Bridging table has two columns

As the relationship diagram shows, there is a 1:Many relationship from the Accounts and ChartPL tables to BridgeChartPLAccounts.  We can simply modify the [PLActual] measure that we saw earlier with a table filter from BridgeChartPLAccounts.   

PLActual =  
   CALCULATE (  
      SUM ( Transactions[Amount] ), 
      BridgeChartPLAccounts, 
      ALL ( Dates ), 
      Dates[Date] <= MaxDate, 
      Dates[Date >= MinDate 

At first glance, you may be surprised that this works, given the directions of the cross filters in the table relationships. It is, however, a feature of Power BI and when you think about it, this is how you get the great interactivity between the visuals in Power BI where we can select an element of a child table in a visual to filter the parent table. 

Apart from making your Profit and Loss Measures simple to write, this technique means that you can have two visuals on the same page; one driven by ChartPL and the other driven by Accounts and you still get the full Power BI interactivity between the visuals when you select the Gross Profit subtotal.  You can try this here.   

In the demo, the P&L statement visual (a matrix) is driven by ChartPL and the variance chart (a waterfall) is driven by Accounts. Notice how the interactivity still works correctly if you select the Gross Profit line in the matrix. 

Notice also in the demo the tooltip when you hover over a value in the Profit and Loss statement. 

PNL tool tip

The row Product Sales in the Profit & Loss statement comes from ChartPL whereas the tooltip comes from the Accounts’ table and the filtering is done by the relationships that both these tables have with BridgeChartPLAccounts. 

How to create the ChartPL and BridgeChartPLAccounts tables 

To create these tables, the general path that I follow is to follow these two steps: 

Create the ChartPL table. In the example used in this article, it is essentially the top two levels of the ‘Accounts’ table with an additional row for Gross Profit and an additional second-level blank row for each of the top levels. 
 
The top rows of ChartPL in this example look like this: 

ChartPNLTable

  1. Note that each ChartPL[Level1] value has a blank ChartPL[Level2] value. This provides the blank row that separates each Level 1 section. 
     

  1. To create BridgeChartPLAccounts, in Power Query, I perform a Cross Join between ChartPL and Accounts to give every possible combination of the rows of the two tables. I then create a filter to retain just the rows that I want to keep. The result is BridgeChartPLAccounts that I can reduce to a simple two-column table to link ChartPL and Accounts.  

For Gross Profit, all the GL accounts that contribute to Gross Profit are mapped to it in BridgeChartPLAccounts, yet in ChartPL there is just a single Gross Profit line on Level 1, with a blank row on Level 2 to give the spacing before Overheads. 

Summary 

Adding Gross Profit and other irregular subtotal lines into your Profit & Loss statement in Power BI is a little trickier than in Excel, but it is not difficult.  The technique that I have presented here with the two extra tables is not the only way to do it, but it my default method.  It keeps the DAX code simple, performs well, gives freedom over how to lay out the rows of the P&L statement, and retains that nice Power BI interactivity between visuals, tooltips and drillthrough pages. 

Useful links 

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By AndrewV12
26th Sep 2021 16:49

Well it all sounds good.

Thanks (0)