Profit & Loss–The Art of the Cascading Subtotal

I occasionally look through the logs of what people were Googling (or Binging etc.) when they found their way to the blog.  Turns out that there’s a lot of accounting and finance related topics.  And while I know my way around Excel and PowerPivot pretty well, I have never directly worked in those industries.  I could never, ever, produce a credible post on something like a Balance Sheet, or P&L, or PB&J.  (OK, actually, I can handle that last one).

So I asked David Churchward if he’d be willing to write an occasional post on these topics.  He has graciously agreed, and what appears below is the first such post, on Profit and Loss in PowerPivot.

Guest Post from David Churchward:  P&L in PowerPivot

As a fellow of CIMA, the first report that I wanted to create in PowerPivot was the infamous Profit & Loss (or P&L if you prefer).

It turns out, this wasn’t the simplest report to conquer first as there’s some hidden complexities. In this post, I’ll walk through how to create one version of a P&L but you should find some of the techniques useful elsewhere and transferable to other Profit & Loss layouts.

P&L reports can take a number of forms, but the approach that I will walk through here is easily transferable to any layout that may be required. To illustrate this, I’ll create the simple layout shown to the left (with a bunch of fake data).

In reality, this report should carry percentages and comparatives to prior year and budget, as well as show periodic values and year to date values. Perhaps I’ll come to all of that in a future post, but for now, the backbone of the report is in the calculations in the Cascade_Value_All column of this report.

The Problem

As you can see above, each section of the report can behave in a different manner to the others. To explain this, I’ll briefly explain the different section types:

Sales and Cost of Sales – these are broken into subsets based on the product groups sold (Air, Rail, Sea in this example). This is a simple sum of the underlying transactions within these categories.

Gross Margin – this is a subtotal of Sales and Cost of Sales (ie Sales less Cost of Sales or Sales plus Cost of Sales if these groups have different sign conventions) and, once again, this is broken down by the same product types held in our sales and Cost of Sales sections.

Sales Costs, Administration Costs and Directors Costs are a sum of underlying transactions and are broken down by cost categories such as Salaries, Travel expenses and so on.

Operating Profit is Gross Margin less Sales Costs, Administration Costs and Directors Costs. A breakdown of this value is possible, but probably wouldn’t be shown on a P&L report so we simply require one subtotal.

Profit Before Interest & Tax = Operating Profit less Non Operating Costs

Profit Before Tax = Profit Before Interest & Tax less Interest costs

Net Profit After Tax = Profit Before Tax less Tax costs

The above feels like a whole lot of different measures especially since each section can carry a further breakdown or simply show a subtotal.  In fact, it can all be done with one relatively simple measure and a little bit of data conditioning. Whilst this can be written as one measure, I’ll build this up as a series of measures to illustrate the method but the report ultimately displays as one measure.

The Art of the Cascading Subtotals

Given the above problem, let’s break this down into simple “Excel” type equations. Our dataset essentially contains 6 types of transactions.

[Note – Sales and costs have different sign conventions- Sales are negative and costs are positive – I’ll come onto this – it’s an accountancy thing!]

The 6 types are Sales, Cost of Sales, Costs (although we have 3 types being Sales Costs, Administration Costs and Directors Costs), Non Operating Costs, Interest, Tax

The values we want to show are as follows

Sales = sum(Sales)

Cost of Sales = sum(Cost of Sales)

Gross Margin = sum(Sales) + sum(Cost of Sales)

Costs = sum(Costs)

Operating Profit = Gross Margin + sum(Costs)

Just to interrupt the flow – can we therefore write Operating Profit as below?

Operating Profit = sum(Sales) + sum(Cost of Sales) +sum(Costs)

The answer is yes which means that our report is essentially summing all transactions in our report that precede it. Could we therefore use a derivative of the running subtotal concept? My method here is very similar to that of a running subtotal and this is what I tend to refer to as the Cascading Subtotal.

How It’s Done

We have our fact table which is a series of transactions representing sales or costs and each carry identifiers to determine which type of sale or cost they are. My fact table is called FACT_Tran

Heading1_Code is linked to a heading dimension table called DIM_Heading1 as shown below. DIM_Heading1 carries all of my primary report headings.

I’ll come onto the meaning of the columns Heading1_Summary and Heading1_Show_Detail fields in these tables shortly.

The field Heading2_Code in my fact table is linked to a heading dimension table called DIM_Heading2 as shown below and this table carries all of my secondary report headings.

You can go beyond this with third, fourth, fifth……… levels if you wish.

Aside from that, we’re only carrying dates and a customer field in our fact table. These will be referenced when time intelligence and slicing our Profit and Loss comes into play which I’m not going into for now.

First let’s deal with the back to front nature of Accounting Transactions

Nothing revolutionary here, but we have to deal with the fact that sales transactions are processed in the accounts as negative values and cost transactions are processed as positive transactions. We simply create a base measure which reverses this. Let’s call the measure Value_Corrected

Value_Corrected = SUM(FACT_Tran[Value]) * -1

I won’t dwell on this, it just needs dealing with. Let’s get on with some proper DAX!

But First – A quick trick!

Accountants like to be able to switch their reports between whole numbers, numbers represented in thousands and sometimes in Millions (I’ve never got to Billions but watch this space!).

Create a table called Divide_By with the values that you will divide by (therefore a good name for the table) and give each record a name.

You don’t need to link this table to anything but create the following measure called Selected_DivideBy on that table.

On our fact table, create another simple measure, let’s call it Report_Value, which sums our value column and divides by the result of our Selected_DivideBy measure.

For any other measures, reference this Report_Value measure and you’ll then be able to apply a slicer to all values that will change how they’re represented.

This can be used for numerous applications including currency translation although a Slowly Changing Dimension is probably more suited to currency and I believe this can be done in PowerPivot but I haven’t tried as yet.

Now Some Proper Measures

If I simply use my Report_Value measure, I get the following once I bring in my headings from DIM_Heading1 and DIM_Heading2

My transactions are summing correctly, but I don’t have any values for Gross Margin, Operating Profit or any other key P&L subtotals. I need to therefore create a measure which creates values for these subtotals. Consider our DIM_Heading1 table again and refer to “The Art of Cascading Subtotals“

You’ll note that my first field in this table is an increasing integer. If we take Gross Margin as an example, which has a code 3, we need to sum everything that precedes that code (ie Sales and Cost of Sales). That therefore implies that I have to override the Heading1_Code link using an ALL function and then filter the dataset where Heading1_Code is less than 3. This measure looks like this:

Cascade_Subtotals

=CALCULATE
(
[Report_Value],
ALL(DIM_Heading1[Heading1_Name]),
DIM_Heading1[Heading1_Code] < VALUES(DIM_Heading1[Heading1_Code])
)

You’ll notice that we first remove the link dependency using the filter

ALL(DIM_Heading1[Heading1_Name])

and then re-apply a filter that will return a dataset where Heading1_Code is less than the code being evaluated on our report

DIM_Heading1[Heading1_Code] < VALUES(DIM_Heading1[Heading1_Code])

So, what happens when we use this measure on our report?

Ummm, it appears to have all gone wrong! Actually, it hasn’t. In isolation, this measure won’t work as DIM_Heading1[Heading1_Name] is presenting more than one answer to each equation evaluation. However, we can deal with this by instructing the measure to only evaluate when there is only one Heading1_Name on the report using Countrows and Values. I won’t go into these functions in detail here as Rob has already done an excellent job on these here and here and here.

Cascade_Subtotals
=IF(
COUNTROWS(VALUES(DIM_Heading1[Heading1_Name])) = 1,
CALCULATE
(
[Report_Value],
ALL(DIM_Heading1[Heading1_Name]),
DIM_Heading1[Heading1_Code] < VALUES(DIM_Heading1[Heading1_Code])
),
BLANK()
)

With this in place, we get the following:

So we now have a bigger mess, or so it seems. Our subtotals such as Gross Margin and Operating Profit are now calculating correctly, but we’re getting a lot of mess in between. For instance, Sales Costs are actually displaying Gross Margin values and Cost of Sales is showing Sales Values. Operating Profit, which I would like to see as just one total is actually populated with a secondary level of detail that I don’t want. If I expand the line, I get the following:

This may have some value in certain reports, but not on my Profit & Loss report here.

It should be noted that one of the values on each key row on this report is correct as it stands. We therefore simply need to evaluate when to use each one. This is where our Heading1_Summary field comes into play on our DIM_Heading1 table.

You’ll notice that I’ve flagged all of the key Profit & Loss subtotals with a 1. I know, that when my Heading 1 is carrying this flag, I want to use my Cascade_Subtotals measure. I can code this using a simple IF statement as follows:

Cascade_Value_Heading1_Summary
=IF(
MAX(DIM_Heading1[Heading1_Summary]) = 1,
[Cascade_Subtotals],
[Report_Value]
)

This gives the following effect on my report

All of my key subtotals are now calculating correctly and I’m also showing the correct values in sections where a cascaded subtotal isn’t required. Job done? Well, not quite. You’ll notice that my key subtotals such as Operating Profit are still carrying details underneath them that I don’t want to show.

To get around this, I need to explicitly tell my measure that I want my Report_Value measure to execute at levels below my key subtotals (ie for Heading2_Name such as Air, Rail and so on). I sense that COUNTROWS may be able to do this for me so let’s try a quick measure to see what this would deliver using the following measure:

Countrows_DIM_Heading2_Name
=COUNTROWS(VALUES(DIM_Heading2[Heading2_Name]))

I’ve collapsed some of the screenshot below to show the key areas

You’ll notice that this measure evaluates to a value of 1 for each heading 2 (irrespective of whether there is a valid value on the report or not) and a value of 9 for all subtotals. I can therefore distinguish between a subtotal and a heading 2 line on my report using

COUNTROWS(VALUES(DIM_Heading2[Heading2_Name])) > 1

Let’s build this into our measure

Cascade_Value_Countrows

=IF(
MAX(DIM_Heading1[Heading1_Summary]) = 1
&&COUNTROWS(VALUES(DIM_Heading2[Heading2_Name])) > 1,
[Cascade_Subtotals],
[Report_Value]
)

Great, my Operating Profit section now doesn’t carry any detail. However, I’ve also lost the lower level detail for Gross Margin. I need to put that back. To do this, I have a flag on my DIM_Heading2 table called Heading1_Show_Detail.

I can now tell my measure to behave differently for my Gross Margin section by adding the condition:

MAX(DIM_Heading1[Heading1_Show_Detail]) = 1

When I build all of this together, I get a measure that looks like this:

Cascade_Value_All

=if(
MAX(DIM_Heading1[Heading1_Summary]) = 1
&&(MAX(DIM_Heading1[Heading1_Show_Detail]) = 1
||COUNTROWS(VALUES(DIM_Heading2[Heading2_Name])) > 1
),
[Cascade_Subtotals],
[Report_Value]
)

My measure is now correct. When I remove all of the others and tidy up, I get the following:

Quick Recap

In summary, we’ve basically been through the following steps

Value_Corrected = Created a base measure which reverses the accounting signage of transactions

Report_Value = Taking our Value_Corrected measure, we’ve adjusted for the fact that our report should be able to flex between GBP, Thousands and Millions

Cascade_Subtotals = we’ve created an alternative measure to Report_Value that we can use at Subtotal levels in our reporting using the Cascading Subtotals method

Cascade_Value_All = we’ve then conditioned our report to decide when to use the Cascade_Subtotals measure and when to use the Report_Value measure by using flags on our heading dimension tables and COUNTROWS functions to highlight subtotal levels.

CLICK HERE TO DOWNLOAD THE WORKBOOK

What Next

There’s still a lot we can do to this report such as showing comparatives such as Budget and Prior Year. We can also overlay time intelligence to show periodic and YTD values based on a time slicer. We should include key percentages such as Gross Margin percentage and Return on Sales for key sections of the report. When this is done, you can create a report similar to one I created some time ago which looks something like this (although hacked to preserve sensitive data)

One Last Point to Note

There’s numerous ways to construct these reports. In truth, I do a lot of data conditioning in SQL before I even touch PowerPivot which opens up a whole raft of extra possibilities. Taking account of Rob’s excellent post Less Columns, More Rows = Speed, an alternative is to simply accept that rows are cheap in terms of performance if your measures are optimised so there’s the possibility of bringing in the same dataset multiple times but displayed as different report headings. This is a method that I use widely and perhaps I’ll run through this in a future post if there’s the demand for it and Rob allows me to waffle on again.