skip to Main Content

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

PandL Screenshot1As 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:






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


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


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.




COUNTROWS(VALUES(DIM_Heading1[Heading1_Name])) = 1,





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




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:



MAX(DIM_Heading1[Heading1_Summary]) = 1,




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:



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



MAX(DIM_Heading1[Heading1_Summary]) = 1

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





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:



MAX(DIM_Heading1[Heading1_Summary]) = 1

&&(MAX(DIM_Heading1[Heading1_Show_Detail]) = 1

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






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.


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.

This Post Has 44 Comments
    1. David – Wow. Excellent article! This will be useful. Thanks for sharing.

      J – You might have missed the link to the sheet in the Quick Recap section. When I downloaded it had a .zip extension. If you rename the file with a .xlsx extension you can view the workbook and PowerPivot data model.

  1. David,

    Great article with some good techniques that can be applied to other areas. I can’t believe how complex it is to set this standard P&L report up. It might be an example where MDX or as you say SQL conditioning is applied.

    In any case I see you’re showing how PP can be stretched. Well done.

    Keep up the great posts.


    1. Thanks Lee. I think there’s 3 levels of complexity here:

      1 – Relatively straight forward – conditioning the 2 key formulas (Report_Value and Cascade_Subtotals)
      2 – Slightly Complex – Understanding the Cascade Subtotals concept (once it clicks, it really clicks) and conditioning the formula to chose which one to use
      3 – a bit more complex – conditioning when a measure should display level 2 analysis and when it shouldn’t. It’s worth reading Rob’s brilliant “Iffer-Blanker-Filter” post as this concept if very similar.

      If I can find chance and it lives up to Rob’s high standards, I’ll write up the same concept based on pre-conditioned data from SQL stored procs. Watch this space!


      1. David,
        Thanks for sharing your posts on this-I can’t wait to try it on my own data. If you’re willing, it would be great to get your take on pre-conditioned SQL data. I still don’t have a good feel for when to pre-process vs. manipulate in Excel.

        Thanks again for the info,

  2. Hi David

    Great post. I used already to train some of our new graduates hires on PowerPivot and financial reporting.

    Are you planning another post on balance sheet and cash flow?



    1. Hi Enzo

      I’ve got a few more on the boil with some great PowerPivot Finance techniques to come. They will, of course, be posted here so keep an eye out.


  3. David,

    Much appreciated – finance is an area that doesn’t have nearly enough PowerPivot (or even plain vanilla pivot table) info on the web. Many financial services professionals (equity analysts, investment bankers, etc.) who do a lot of slicing and dicing of financials would benefit immensely from the pivot table features in Excel 2010. However, we generally face the issue that we generally don’t receive financials in database format – financials are generally formatted with each reporting period in a separate column, and generally, security analysts don’t have access to the source data the way an accountant would. If you could publish a primer on how to convert a standard report into a more pivot-friendly format, it would open up a world of possibilities for analysts.

    1. ETL is a hugely important aspect in conditioning data before PowerPivot takes over. This depends on your setup and the tools that you have available to you. You can condition DAX to work with the data you have but this is more cumbersome and impacts performance. ETL can be done in SQL,MS Access and others using automatic packages but Excel / Pivottables and PowerPivot would have a manual element in the ETL process. However, there is a new concept coming down the line that is referred to as Power Import(officially known as Montego / MS Codename Data Explorer at the moment) which may prove useful but I don’t know much about it at the moment. I’ll look into a post on ETL and priming of data to “unpivot” but this is a huge topic that needs to be considered very carefully in your environment.

  4. Great Article David – Thank you! I’m trying to get it to work with 3 and 4 heading levels – have any examples with that many levels? Thanks!

    1. Hi Fiona

      If you have 3rd and 4th heading codes on your fact table, you can link to a heading table that relates to these heading codes (as per headings 1 and 2 in this example). You can then simply bring the heading names into the rows section of your pivot table. All of the calculations should still work. Heading 1 is the only heading where cascading totals are required because of the nature of a P&L where subtotals such as Operating Profit are required. The other headings simply work as subtotals within their own sections and I would expect lower level headings to work as per heading 2. It is posible that you might want to do something different with heading 2 (ie if you have profit centres).

      If you have any specific requirements where a subtotal works differently (ie doesn’t simply subtotal the elements in that section),let me know and I’ll gladly help if I can. Give it a go and if you hit any brick walls, give me a shout. Good luck.


      1. Hi David,
        Thanks for your explanantion. I tried it and I do get my third level showing up however now my second level items are showing total lines – so their lines are all being duplicated. I’m trying to fix it but so far I’m unsuccessful. Any help you can offer would be greatly appreciated! Thank you!

        1. Hi David,

          This is now resolved – thank you so much for you help! Your new measure:

          did the trick!

          Fiona :->

  5. Hi David,

    I found your post very helpful. I was able to recreate a P&L with 2 levels but I am stuck on adding an interim level that is not a cascading total of all levels above but only a subset of levels. For example, Heading 1 includes Revenue, COGS, Gross Profit (summary line), Admin Expenses, Sales Expenses, Total Expenses (summary line but only for Expenses), Operating Income (summary line), Taxes, Net Income (summary line). The Total Expenses summary is cascading for all accounts where I need it to only sum the Expenses. Any advice would be appreciated.

    1. I’m having the exact same problem. The solution presented does not provide a sum of the expenses, which is a very common P&L presentation. Has a solution to this problem been proposed?

      1. I am trying to do the same thing in PowerBI. Everything is working fine, but I can’t seem to find a way to show the Total Expenses (just expenses) Did anyone find a solution for this?

  6. Just learning about PowerPivot. Trying to duplicate your model here. Am getting the follow error(s) on both Report Value and Cascade Subtotals calculated fields:

    Calculation error in measure ‘GLDataMartTable'[Report_Value]: The value for ‘GL Amt Report’ cannot be determined. Either ‘GL Amt Report’ doesn’t exist, or there is no current row for a column named ‘GL Amt Report’.

    Calculation error in measure ‘GLDataMartTable'[Cascade_Subtotals]: The value for ‘GL Amt Report’ cannot be determined. Either ‘GL Amt Report’ doesn’t exist, or there is no current row for a column named ‘GL Amt Report’.

  7. Doesn’t anyone else think this is far too complicated for most average excel users? I didn’t realize Powerpivot was supposed to be the domain of expert modelers only.

    1. There’s a curve. I myself VERY rarely build something this complex. And yet, I build absolutely amazing things every day, without ever approaching something this opaque.

      Just like Excel itself, you don’t have to know how to do everything in order to derive tremendous value. Power Pivot is ironically penalized (sometimes) for being “overly deep” as a product.

      1. Right but in my experience so far tasks that are simple in ordinary excel, such as calculating subtotals (but other tasks too), are ridiculously complicated in Powerpivot.

        I have no problems with learning curves, and being deep; but I do have a problem with products that were designed by techy people for techy people (and I am a techy person myself).

        1. You mean subtotals in calculated columns? Yeah those are much harder than in Excel, agreed. I recently recorded a lesson for PowerPivotPro School that says precisely that.

  8. Nice post. One thing that accountants frequently use is a column for percent of sales (on all lines of the income statement). Any thoughts on how to make that work with a power pivot? I’ve tried in regular pivot tables to do a calculated field and it will work until you get to a subtotal row and then fail or I did another approach that worked until I got to a subtotal column and then it failed. I’m pretty sure this can be done in powerpivot but I’m not sure how.

    1. Try something like this:


  9. I just discovered this whole “PowerPivot” thing, and I’m intrigued. Reading this post I see this comment from way back in Sept 2011.

    “If I can find chance and it lives up to Rob’s high standards, I’ll write up the same concept based on pre-conditioned data from SQL stored procs. Watch this space!”

    Did this write-up ever happen? My struggle with PowerPivot is basically how much do I do in SQL, and how much do I let PowerPivot do? I would think, from a performance perspective, that it would be preferable to write SQL stored procedures that do the bulk of the heavy lifting, and let PowerPivot act as a very user friendly front end. I, for one, would love to see this very same example based on pre-conditioned data from SQL stored procs.

  10. Hi David

    An excellent article – is there any way to get a download of your P&L power pivot in your other article in full as its exactly how I want to recreate it [Prior, Actual, Budget etc] as we use the same format already and it would save the recreation time.

    Any help./assistance would be appreciated.



  11. Hello, I’m very much interested in the more advance report that you presented in the end. Can you give some insight into that?

  12. Is there a way to create cascading totals across columns? For instance, I have a Fact Table with rows for income and rows for expense. I have a linked Header Table that, in addition to a row for Income and a row for Expense, has a row for Net Income. I want to use Income, Expense and Net Income as Column Headers in a pivot, where Net Income sums Income and Expense. What would the measure be? Thanks.

  13. Hello,
    How do i create a calculated field that when i filter, for example “2016”, it generates the sum of the previous year?

  14. I’m amazed that this article dated 2011 is one of the best i can find to use PP to resolve a P&L, CF & BS.
    I’m just working on a massive P&L with endless reporting lines and many different levels of subtotals that unfortunately do not cascade nor have a loop pattern to ‘cascade’ on.
    Would it be correct to create a unique measure for each of these subtotals. eg Travel costs subtotal measure = hotels + car + airline.
    I can do it but i then need the ‘actual’ version of that measure, the budget version, the rolling ‘actual or budget’ version and then the relevant 5 different time dimension versions. That’s 15 measures per subtotal which is just not efficient, surely there must be a better solution ???
    It just means loads of measures, datamodel gets slow. Is there not a more efficient way to resolve this ?

    1. I did my P&L using cube formulas instead. Financial reports for me are mostly static with respect to the number of detail line items. I setup the cubes for the “lowest” level of details, then just used basic excel SUBTOTAL() functions to do the various rollups. Can’t expand/collapse as easily but for me it is not that big of an issue.

  15. This is a great workaround, but I’m in an environment with multiple departments with different subtotals which need consolidating, so can’t get my mind around how the cascade would work in this situation.

    I suppose if I list all the different departmental totals and subtotals I need in category sequence together in the Headings table (i.e. all the sales totals for all depts first, then cos, then gross, then expenses) if there isn’t say Heading1_Code number 8 for the chosen department the calcs will still resolve correctly since it will roll up everything < Heading1_Code whether number 8 is there for that department or not?

    Will have a go and post if I get it to work.

  16. Great post and great comments, It feels like entire finance community is wrapping their heads around this tool trying to accomplish something so basic as a P&L, we are not asking for much, are we 😉

  17. Thank goodness for the Internet and this website! More than 5 years after this was posted, it is still very good information. I found a video on YouTube that was very good and had similar concepts. This article reinforced the concepts and provided more explanation to enhance my understanding of this subject. It was particularly useful in explaining the whys and how to avoid the “multiple table” error message, which I had one heck of a time finding a solution for.

    As Excel moves from purely a ledger application to a data based application, articles like this are very important in helping people keep up with the times and new uses for this workhorse application.

    Thank you again for taking time to post this.

  18. HI David,
    Great article and has been helpful in successfully developing various PnL on Power BI.
    However, i have a peculiar challenge and cant seems to get pass it.
    The source data in view, has its PnL values as cumulative, i.e monthly cumulative values i.e typical Balance sheet display of values
    I have successfully built the PnL thanks to your article, each month in the PnL report displays the correct value (i.e individual month value and not cumulative month value).
    However, i noticed the grand total shown is inaccurate. seems to be summing up the cumulative monthly values rather than the individual monthly values which i have catered for by deduction of previous month value from the current month value.

    As thus:

    Actual = ([Act] – [Actual_Previous_Month])

    where Act = IF (
    max(Template[Calc_Type]) = 2,
    [R.Total] ,

    and Actual_Previous_Month = CALCULATE([Act], PREVIOUSMONTH(‘Date'[Date]))

    Please any clue here???

  19. Interesting solution. Has any one extended this to the Balance Sheet? And here is the challenge I have encountered; how do you add deal with a situation where you have to have a ‘Total Expenses” subtotal?

Leave a Reply

Your email address will not be published. Required fields are marked *