skip to Main Content

From the Water Cooler

As a ‘water cooler’ of sorts for this community, we meet some amazing people. Matt Mowrey shares with us a really useful technique to create a discoverable income statement using PowerPivot.  This post runs a little longer than usual to describe the technique, but totally worth it.

A Problem Applying PowerPivot to Income Statement Reporting

When I was first introduced to PowerPivot five or six years ago, I knew it would serve me well in my FP&A function. I spent a lot of time learning about PowerPivot and DAX,with the goal of programming our host of reports, including a discoverable income statement pivot table. This report, while not the prettiest, is highly useful for variance analysis.

Please click here to download the workbook to follow along.

In it’s earliest phases, it looked like this:

With Dimension Members

Or this:

With Measures

The crux of the challenge may be obvious: is the best practice to use dimension members (top graphic) or measures (bottom graphic) to create my income statement? Either way, both are U-G-L-Y and something was missing. Coming from an FP&A background, I wanted to mimic the Excel interface of a budgeting and planning system as much as possible. This would mean one value (measure) in the values box of the pivot, then a layering of row/column dimensions at the intersections of the reporting requirement.

Searching for a solution led me to the P3 blog and David Churchward’s posts on The Art of the Cascading Subtotal. With practice, the pattern finally sunk in and I got the cascading subtotal “mechanism” to work, but it still wasn’t giving me the result I was hoping for. I won’t go into specifics, but I basically needed more control over my calculated members (e.g. gross profit, operating profit, net profit) and the way they displayed.

There’s already so much greatness in the query/ETL (Power Query), data crunching (PowerPivot), and display mechanisms of Power BI. But occasionally, I stumble across key concepts that revolutionize the way I approach my daily work. In this case, it’s an understanding of a simple star schema and using DAX measures within a SWITCH statement.  These two concepts drive what I’m about to explain.

I often find that if a title resonates with me, it helps me internalize and make it a more permanent member of my arsenal. I call this one “A Star with a SWITCH”.

Starting Out: What a “Typical” First Pass of an Income Statement Data Model and DAX Measures

Below is a quick look at my starting point, which very much resembles the architecture of a planning and budgeting system. The fact table is on the bottom (General Ledger) and dimensions are on top, just as Rob teaches in his classes and books. I find that keeping visually organized is paramount.

In addition to the tables below, there’s a calculations table not pictured.

Data Model
Data Model

The first measure I always write is a harvester measure summing the values. There is an amount column in my GL fact table so the measure looks like this:

SUM GL Amt :=
SUM ( ‘General Ledger'[Amount] )

In my data model above, I have a sign issue that requires fixing (P3’s cascading subtotals post also had this issue). I personally like working with positive numbers; it’s also the way my company reports, so I want every number, whether income or expense, to be positive. My account dimension has a “report sign” column that lists a positive 1 or a negative 1 next to each account. The measure I write to convert my harvester measure uses the SUMX function, an iterator, that looks at each individual line and calculates appropriately.

GL Amt (Correct Signs) :=
SUMX ( Accounts, [SUM GL Amt] * Accounts[Report Sign] )

Corrected Signs
Corrected Signs

Because there is a relationship between the Account dimension and the GL fact table, the iterator looks at the report sign of each account row and multiplies the fact data accordingly. If I ever have a tough time determining whether to use an iterator, this example in particular helps me get my head right—I think of it as a warm up. “GL Amt (Correct Signs)” measure is now the basis for everything that comes next.

Take the Time to Define Your Report Requirements Upfront!

Within my income statement, I want to display two scenarios: Actuals and Budget. I’ve experimented with more scenarios than that (PTD, YTD, SPLY all work beautifully), but I’m keeping it simple for this post. Just know that your scenarios don’t have to stop with actual and budget—you can get into lots of great comparative analysis that may blow your end-users’ (maybe your CFO’s) mind.

It’s essential to define display requirements at this point because those will dictate your DAX going forward. Is your income statement a functional view, account category view, or something else? Most people underestimate the power of this step. By the way, in the P3 Finance Level Up class, Austin Senseman presents a pattern that will blow your mind to create these different views without the more manual buildup I’m about to present. I’m perfectly content with my approach because it’s much less manual than the alternatives, but the class is worth checking out for that morsel alone.

To keep things simple, my income statement’s general display is by account categories—a typical income statement. If you’ve written income statement reports before, this is one of the easiest views to write. But, if you understand the basics of this approach, I encourage you to experiment using, for example, the org dimension for a more functional income statement view that includes business unit operating income. I’ve done it and it works beautifully.

This is how I want my income statement to display:

Revenue (with drilldown)
Cost of Sales (with drill down)
Gross Profit (calculated member, no drill down)
Operating Expenses (with drill down)
Operating Income (calculated member, no drill down)
Other Income & Expenses (with drill down)
Depreciation & Amortization (with drill down)
Interest Income & Expenses (with drill down)
Taxes (with drill down)
Net Income (calculated member, no drill down)

Creating the “Display” Tables – Evolving to a Snowflake Schema

Once you’ve nailed down this structure, the next step is to create a table with a row for each of these dimension members, including the calculated members (e.g. Gross Profit). It is this table that will eventually turn our star schema into a snowflake. There are different ways to do this, but for this example, I’m using a standalone Excel file and Power Query to bring it into my income statement data model. Here is what it looks like in the PowerPivot view:

Header table
Header table

Whenever I create my own dimension, I automatically number the rows with an ID. Let’s sort the Header on the ID column:

Sort By Column

I created a “Summary” and  a “Show Detail” column. We’ll use the “Show Detail”column as a toggle in the DAX programming to tell the report whether to—you guessed it— show detail or not. You’ll notice that there are no “1’s” by the calculated members, because there is no detail to show.

You can build out groupings to your heart’s content with this pattern. I’m also categorizing accounts with a subheader (child of header), for a total of two groupings in this model. When you reach the lowest level of your desired groupings, you need not include the summary and show detail columns.

Subheader table
Subheader table

The next step is to categorize each of your accounts in the account dimension with a header and it’s subheader. No account will have the name of a calculated member. In my account dimension, the column names are “Header” and “Subheader”.

Accounts with Header and Subheader
Accounts with Header and Subheader

I maintain my account dimension in an Excel file and use Power Query to bring it into my data model. If we change the categorization of, say, an expense, we revise the Excel file then on refresh, the Power Query brings the revised header into the data model. My account Excel file is refreshing centrally against a SQL table, so as new accounts are added, the appear in the file with a blank header. I’ve experimented with many ways maintaining dimensions that change often like account, but this is my favorite as of this writing.

Here is a quick look at where my data model stands now that I’ve added the header dimension and went from a star to a snowflake schema:

Modified Star schema
Modified Star schema

Writing the DAX Measures

We’re getting to the exciting part, but first, you need a DAX measure for each of the reporting lines we outlined as our requirement above. This is the point where you’ll want to clear your schedule, put on your headphones, and get in The DAX Zone. It doesn’t get much easier than looking at your reporting line requirements, making header and subheader tables based on those requirements, and  then programming the DAX for the headers that aren’t calculated members.

If your brain has temporarily DAX-atrophied because we spent a couple paragraphs on filling out our data model, here’s where we’re going…

First, we break out the “base measures” for each of those scenarios:

Actual Amt :=
CALCULATE ( [GL Amt (Correct Signs)], Scenarios[ScenarioName] = “Actual” )

Budget Amt :=
CALCULATE ( [GL Amt (Correct Signs)], Scenarios[ScenarioName] = “Budget” )


Next, the measures highlighted in the red box correspond to the reporting line requirements by scenario.

When I program DAX measures like these, I try to keep the description short. These measure names will never be visible on reporting, so it’s nice to keep the names as short as possible.  This is going to help keep the coding and reviewing of your DAX easier to read.

Here’s a look at Rev Act, CoS Act, and GP Act:

Rev Act :=
CALCULATE ( [Actual Amt], Headers[Header] = “Revenue” )

CoS Act :=
CALCULATE ( [Actual Amt], Headers[Header] = “Cost of Sales” )

GP Act :=
[Rev Act] – [CoS Act]

I’ve given you three examples above; at this point, you would continue programming measures to fill out your scenarios as I previously outlined in my report requirements.

One word of caution: the “header” you name above (highlighted in yellow) must be in the Header dimension, not the Account dimension. Including building the data model for this post, I’ve driven myself crazy when the outcome didn’t work. I checked and re-checked everything. I had to go back through each of my DAX measures and change them—total time to change was less than three minutes. Auditing your work always builds analytic character and makes you awesome! Don’t underestimate the power of mistakes.

NOW, we’re at the good part—where the magic happens. I’ve often called this the Greatest Formula in the World (GFITW), but I know others have different ideas. By the way, the original GFITW has worked wonders for me and can be applied to this pattern for, say, a trailing twelve months (TTM) scenario.

The Magical Use of SWITCH

Let’s take a moment and talk about SWITCH statements. Colin Banfield’s post goes into much more detail, but in general, SWITCH statements are DAX’s replacement of embedded IF statements. Embedded IF statements are hard to read, audit, and they’ll make your spreadsheet real clunky real fast.

The syntax of a SWITCH statement is this:

SWITCH ( expression, value1, result1, value2, result2, else )

Prior to this application, I used SWITCH statements largely to clean or normalize columns, so the expression was usually a column reference.

Listen closely: you can enter a DAX measure in the SWITCH result—it doesn’t have to be text! Colin points this out to some extent in his blog post, but the application for my work didn’t’ sink in until much later.

Let’s look back at the sets of DAX measures we created for each scenario (actual and budget) on our income statement. For each of those sets, we’ll need two additional DAX measures —a subtotal and a total.

Here is the syntax for the subtotal:

IS Subtotal Act :=
IF (
    COUNTROWS ( VALUES ( Headers[Header] ) ) = 1,
    SWITCH (
        VALUES ( Headers[Header ID] ),
        1, [Rev Act],
        2, [CoS Act],
        3, [GP Act],
        4, [OpEx Act],
        5, [OI Act],
        6, [OI&E Act],
        7, [D&A Act],
        8, [II&E Act],
        9, [Taxes Act],
        10, [NI Act],
        BLANK ()

You’ll notice that the main table reference here is my header table.  I had already determined that I wanted part of my rows on my final income statement result. The Header ID aligns to a row display heading and the result is the corresponding DAX measure. David Churchward used COUNTROWS in cascading subtotals and referenced several of Rob’s posts that go into further detail. Here is the one I think is most relevant: More Fun With “IF(VALUES())”. The “100” in the else statement is just a number I put there to quickly find where my logic isn’t working the way I want it to.

Let’s start building our pivot and use the subtotals measures to see what we get.

Pivot Table Architecture
Pivot Table Architecture
Pivot Table Result
Pivot Table Result

My income statement displays in the proper order—yea! But wait, the calculated members are somehow picking up funky detail—boo! So we create the second “total”measure to remedy this issue.

Actual Total :=
IF (
    MAX ( Headers[Summary] ) = 1
&& (
            MAX ( Headers[Show Detail] ) = 1
|| COUNTROWS ( VALUES ( Subheader[Subheader] ) ) > 1
[IS Subtotal Act],
[Actual Amt] )

This measure makes use of the summary and show detail “switches” we built into our header table.

Let’s have a second look at the Header table:

Header Table

You’ll notice that my calculated members are not indicated to show detail. The totals measure effectively skips over them and shows detail for the areas that actually have the detail to analyze. At this point, you would create the same two measures for each of your scenarios.

And the result is this:

Just the right level of display!
Just the right level of display!

Notice that the calculated members (in grey) no longer have incomprehensible detail, but the other categories—those for which I want detail—do.

Adding Variance Columns

Finally, you need some variance information in currency and percentage. In my early days as a financial analyst, one of my biggest report programming challenges was variance convention. If your company hasn’t settled on one and you find your variance signs going different ways on reports, I’d suggest getting everyone to agree on an approach or, at the very least, make sure you footnote your work.

Variance $ :=
IF (
    MAX ( Headers[Var Display] ) = 1,
[Actual Total] – [Budget Total],
[Budget Total] – [Actual Total] )

Wait, what? I didn’t have a “Var Display” column on my headers table. I know. I just added it. And if you’re using Power Query as mentioned earlier to maintain the header and subheader dimensions, it’s as easy as opening that file, adding a columns and “1s”, saving, refreshing your table in your PowerPivot model. It’s crazy-easy to add stuff as you develop your approach. This is what my header column looks like after I add the “Var Display” column:

Header table with variance display
Header table with variance display

Variance %, I’ll admit, was a little tricky at first. What happens if you divide—using the “/” sign—the Variance $ measure you just created by the Budget Total? You get NUM errors. Wrapping that equation in an IFERROR doesn’t help. If you are dividing numbers in PowerPivot, get into the habit of using DIVIDE. Rob touches on the exact reasons in Divide and Conquer.

Here’s what we’ve all been waiting for, the final product:

Final Pivot table architecture
Final Pivot table architecture
Final Pivot table results
Final Pivot table results

Verifying Your Results

I cannot stress this step enough. You MUST make time for and devise a system to audit the results of your report. As nifty as I find this way of writing a report, the ability to restructure the display of your data also increases the potential for losing something — you must be meticulous. You may have experienced a sense of euphoria when you have something that appears to be complete and you can’t wait to pass it on. I’ve done this a time or two only to have inaccurate results and, consequently, my consumers automatically lose all trust in the approach. You don’t want this to happen; you’ll spend more time on damage control and rebuilding confidence than ultimately taking credit for doing something great. Smile

I’m able to retrieve high level numbers directly from our accounting system. I first verify net income; if that ties, chances are I’m probably good, but I usually spot check another line item just in case there is another funky issue. If net income does tie, I start working my way down through the reports “levels”. I find that starting at the highest level and working backwards generally allows me to find the issue. As you’re working down, the awesome thing is that you have pivot table functionality at your fingertips to slice-and-dice ‘till next Sunday! The folks at P3 have been excellent in underscoring this approach through training and consulting, and unknowingly helped me devise my overall audit technique.

Possible Next Steps

Now that you’ve created a discoverable income statement, I would highly recommend upscaling to a Tabular server if you have that resource available. By so doing, you locate your data centrally (rather than in an Excel workbook or PBIX) which helps facilitate an enterprise-level solution and one version of the truth. Users can connect to the Tabular server using not only *native* Excel, but also other Power BI reporting mechanisms like visualizations and SSRS. One of the beauties of Tabular is that it looks and feels exactly like the PowerPivot mechanism and is easy to learn if you already have a good handle on PowerPivot. It’s certainly a resume-builder. You can go from Excel user to SQL Server user almost overnight.

I personally feel this approach and all the possibilities are worthy of a mouse-drop, which is the equivalent of a mic-drop at P3. I’m in the process of rolling out a bunch of reports based on this approach and it should satiate some of the drill-to-details craving. I’m also planning to use this approach in some project management tools. I see the application of this approach in many requirements nowadays. If you need to create a discoverable income statement—or other report—I’ve found Star and SWITCH approach to be wildly effective and flexible.

Forget bending spoons with your mind – there’s no money in it.

It takes a special kind of mindset to “bend” data (and software!) to the human will.  As this article demonstrates, we at PowerPivotPro can twist Power BI into a pretzel if that’s what an organization needs. (A robust, trustworthy, industrial-strength pretzel of course).

The data-oriented challenges facing your business require BOTH a nimble toolset like Power BI AND a nimble mindset to go with it. And as Val Kilmer / Doc Holladay once said, we’re your huckleberry.

Connect with the Experts

Matthew Mowrey

Matt Mowrey is the Senior Director of Finance & Analytics at a leading international development consulting services company. He manages FP&A, accounting, and business intelligence for the company’s largest business unit. Matt is particularly enthralled with DAX, M, and SSAS Tabular and uses them to create KPIs, monthly reporting packages, project forecasting tools, predictive financial modeling, U.S. government contractor indirect rate modeling (NICRA), and more. He enjoys traveling and playing board games with his family, and was a Peace Corps volunteer in Russia.

This Post Has 74 Comments
    1. Thank you, David. Much appreciated. It was your post that made me realize PowerPivot’s potential, so thank you for that too!

  1. Nice. I’ve never had good luck with useful pivot table based P&L statements, but this at least looks like it is worth giving it another try.

  2. When or where will the P3 Finance Level Up class be offered that you mention? Sounds like the kind of training I would love to see about taking, is the same content covered in the Advanced DAX class, maybe?

    1. Maybe someone from the P3 staff will chime in If and when the Finance Level Up class will be offered again. I’ve taken both the Finance and Advanced–some concepts taught in both were similar, but definitely not mutually exclusive.

  3. Just one question. You write:
    GL Amt (Correct Signs) :=
    SUMX ( Accounts, [SUM GL Amt] * Accounts[Report Sign] )

    Wouldn’t it be better to write:
    GL Amt (Correct Signs) :=
    CALCULATE(SUM ( Accounts, [SUM GL Amt]), Accounts[Report Sign]=1 ) – CALCULATE(SUM ( Accounts, [SUM GL Amt]), Accounts[Report Sign]=-1 )

    I say this since I understand that SUMX uses more system resources than a regular SUM.

    Nice post and thanks for sharing.

    1. I’ll have to try that! Admittedly, my jury is still out on the use of iterators. Sometimes they solve all my problems without issues and sometimes they cause performance issues and I spend gobs of time rolling formulas back. I feel like I need a little more practice efficiently programming them. Thanks for taking time to make the suggestion.

      1. The second technique is better – but this is further improved:

        GL Amt (Correct Signs) :=
        SUMX ( VALUES ( Accounts[Report Sign] ), [SUM GL Amt] * Accounts[Report Sign] )

        The issue is not SUM, because SUM ( table[col ) corresponds to SUMX ( table, table[col] )
        The problem is that whenever you have a measure ( [SUM GL Amt] ) in an iterator (SUMX) you have materialization, and to reduce it you have to reduce the cardinality of the iterator. Because the dependency is only on Report Sign, you should just iterate the values of that column, if the expression is addictive.

  4. This is quite spectacular. Beautiful. Bravo. I can imagine many places this could end up being useful in reports that I’m asked to design.

    1. Thanks Matt! Yes, definitely a broader application than just income statement authoring. I’ve even used a mini-version of this “on the fly” in meetings and it’s worked well.

  5. Great job Matt! I have used David Churchward’s model a lot (thank you very much David), but I had always missed being able to do Drill-Down and until today I had given up. You have made me very happy with your solution, thank you very much!

    P.S.: I need a clarification, I seem to understand that in the post model the budget and accounting records are in the same table of the general ledger. It is right?

  6. Thanks Raul. I appreciate that. Yes, budget and actuals are on the same table in this example; this is how they’re structured in my company. You should be able to set up the budget measures on a separate table, so long as both your actuals and plan tables are connected through the data model in the same way. Let me know how it goes for you.

  7. Great article. Thank you for mentioning the importance of verifying your answers and auditing the results. That is SO critical – especially when answers LOOK right – they may not always be – or part of them may be right and other parts not. Our company has an older system so there is a TON of bad data from over the years (bad practices) – being meticulous double checking (and triple checking) results has been a lifesaver. I learned the hard way by not always being detailed in that part of the process. 🙂 Excellent point! Wonderful article!

    1. Thank you, Heather! We live in the same world. I’m finding just how overlooked this step is, whether you do financial reporting this way or with some big expensive analytic investment. I teach a couple Excel classes at my company; recently, I started to include an auditing techniques section and attendees really like it. For me, good auditing helps me sleep at night. The great thing about this way is that there are so many pieces-parts to constructing this–especially if you focus on iterative measure development–that you can peel back the onion in a way that gives you confidence and/or pinpoints errors.

  8. Matt this is GREAT! I have a drillable FS, and it works great, drillable etc, however I would love to add say a margin calc to display. The addition of all the measures, then using the index column for not just presentation order, but within the switch is brilliant. Thanks for taking the time to share this.

  9. Love this article, very helpful. I hope to put something like this into practice one day. One thing I would find helpful to read about in the future is how to forecast a P&L statement using this methodology.

  10. Great post, thanks Matthew. Is it possible to elaborate on why you needed to deviate from David Churchward’s “Cascading Subtotal” and use calculated subtotal members instead?

    1. Thank you for the question, James. In trying to keep the post as straightforward as possible, I now realize that the reason I needed this solution didn’t come through! In short, grouping detail was my main issue. An example from a real world requirement: show account detail for revenue and direct cost, but group operating expense by function. I find that it helps to set up a DAX measure tree that mimic’s your desired result (like I did in the post). It’s the ability to create customized members at various dimensional intersections that is different than cascading subtotals. Does that elaborate enough?

      1. Perfect, thanks Matthew. This is very interesting.

        Please correct me if I am wrong, but I see the trade-off (single measure vs. individual total/subtotal measures) between having to continually add additional “behavioural” attributes (e.g. Show Detail = 1 when account detail is required and = 2 when function detail is required) to the Accounts, Header or Subheader tables. All of which add complexity to the “single” measure.

        I love it. I have recently completed a complex Financial Statement and Reporting project where, with much gnashing of teeth, I attempted to achieve the desired results by using ‘single measures’ as far as possible. However, this still required use of SWITCH, ISFILTERED, HASONEVALUE and a clear understanding of where I was on the pivot table (i.e. Total vs Subtotal and Header/Subheader ID). I wish I had thought of a unique measure for each total/subtotal !

        By the way a very similar technique, explained by Derek Rickard, can be found on YouTube

        1. On your understanding, that’s right. The approach is flexible, but you have to–as you said–know where you are on your pivot table. I’m relatively comfortable keeping track of things both in my design and audit, especially with a good iterative measure tree, so I reap much more benefit than incur risk of misreporting. In my design, I try to make it nimble enough for ever-evolving requirements (I’m sure no one else has this issue…ha!)–so far, so good.

          Thanks for pointing out Derek’s YouTube–I hadn’t seen it before. On a quick skim, I see the similarities. I can definitely stand to learn a thing or two because he uses formulas/approaches that are not currently in my quiver. I plan to go back through it more carefully. I believe in Power BI/DAX as a solution for this, so I’m always eager to learn as much as I can.

          It would be interesting to hear more about your project–how you approached it (requirements gathering to delivery/production), what kind of data you had to work with, etc.

  11. Wow – I have just been using switch formula’s in calculated columns. Having multiple measures in the one pivot table column just blew my mind!

  12. Hi Matt,

    Wonderful post. You have a terrific explanation of the process and a very fluid example which follows David Churchward’s early posts. One of my other favorites by David is the Cash Flow Statement.

    A few observations. As I work extensively with Microsoft Dynamics 365 extracting data via DirectQueries to the Datawarehouse, I have noticed it is very tricky to subtotal correctly with COUNTROWS(VALUES()). The COUNTROWS(VALUES()) formula working out of a PowerPivot model (or with a connection to a PBIX via DAX Studio) works beautifully in cascading and subtotaling correctly; however, in my experience, I have not been able to accomplish quite the same result with Power BI using a Matrix visualization. I have tried using ISFILTERED() in the matrix visual, and I eventually landed on creating a separate measure and/ or following some of Marco’s great VAR (variable) examples to facilitate.

    Following on with your’s and Marco’s comments re: meticulous, I have found it imperative, especially when using DirectQuery, to ensure your formula’s are Optimized. I have found Marco’s article useful in optimizing the model – – and another for best practices –

    This is an awesome article. I work a lot with financial and operational reporting using tabular models as I think this is the future of reporting and analytics. I would enjoy bouncing some idea’s off you sometime.

    Kind regards,

    1. Many thanks for the comments and pointing me to those articles, Phillip! We don’t use Dynamics, but it would be interesting to learn more about why COUNTROWS(VALUES()) doesn’t work well. Being that Dynamics and Power BI functionality are both Microsoft products, I would just assume they’d place nice (oh boy, I actually wrote that statement down). For a number of reasons, I personally prefer Excel for both pivot tables and as a “canvas” to develop. I haven’t been impressed with the little I’ve experimented with the matrix visualization as a replacement for pivot tables. Admittedly, I haven’t put this approach into a PBIX to understand better how it would render (I should have by now, it’s not that hard) in both the matrix visualization and others. Also been curious how it would render in SSRS–seems like it would lend well to it.

      I would be happy to bounce around ideas. Any good forum recommendations for that (perhaps Slack)? I’m also down for good ol’ fashioned phone calls and e-mails too. You (and others) can find me here:

    2. Hi Phillip, i agree with you, Matt performed an excellent article. Ive got the same problem that you, I export all calculations to PowerBI and doesn´t work properly, doesn´t show in matrix visual the Headers summary Gross Profit or Operating Income.
      As you suggested, i tried to use ISFILTERED but didnt have success.
      What you suggest to change is this? :

      ActualTotal2 = IF (
      && (
      MAX ( Headers[Show Detail] ) = 1
      || COUNTROWS ( VALUES ( SubHeaders[Subheader] ) ) > 1
      [IS Subtotal Act];
      [Actual Amt] )

  13. Hi Mathew, great article! this is exactly what I was looking for. I recently started using DAX on Power BI and Excel and being in FP&A building an Income Statement was the first thing I tried to do. I’m trying to follow your steps in Power BI but the gross profit returns blank. Any idea on why I’m getting this result instead of the gross profit number?

    Also one last thing that will be great to add is a Gross Margin %, is this possible at all?


    1. Thanks so much, Luis. On the blank GP, are you using Excel or Power BI Desktop? If PBI Desktop, embarrassingly, haven’t tried this methodology in it, so there could be a difference in the matrix functionality. If you’re getting it in Excel–guess what? I have too! But, I can’t remember the fix off the top of my head. You might want to make sure look at your COUNTROWS(VALUES()) and show detail programming to make sure it’s spot on. On the GP Margin %, there are probably a couple ways to do it, but GP Margin % could be another calculated member (as I call them in the article). So, you would have to add it to your Header table (as it’s called inn this example) and add a corresponding DAX measure that would divide GP/Rev. Should work.

      1. Thanks Mathew! I was trying with Power BI Desktop, judging from the the comment below, there’s probably a restriction/limitation on the matrix vizualiation. I’ll give it a try on Excel and try adding the GP %. Thanks again!

  14. Great article. I uploaded the excel example workbook provided into PBI Desktop, but was unable to get the gross profit calculation to work properly using the matrix table. Is this a limitation in the desktop version or I’m I just missing something? Thanks again for this great blog post!

    1. Thanks for the feedback, Mack! Embarrassingly, I have not tried this in PBI Desktop–I really should have by now. For me, personally, the matrix doesn’t not replace Excel pivot table functionality (after getting all excited about a forthcoming “pivot table” in PBI Desktop a couple years back). It’s entirely possible the matrix mechanism prohibits this approach from working.

  15. Should this same approach work in Power BI? I’ve got the numbers showing for the say “marketing” which is defined as Total Marketing = CALCULATE([Total Actual],Budget_L1[Budget L1]=”Marketing”) where “Total Actual” is just the harvester and Budget L1 is my “Header”. However none of my measures like EBIDTA show up at all where this is defined as EBITDA = [Total GP test]+[Total Staff Costs]+[Total Marketing]+[Total Overheads].

    Just wondering if I’m banging my head on brick wall as SWITCH just doesn’t work the same in power BI?

  16. Hi Mike. I still haven’t gotten around to trying this out in Power BI. I always struggled with the matrix visualization and have, more often than not, resorted back to an Excel pivot table.

  17. Unfortunately not an option. One of the guys on Power Forum gave your code a go and seemed to generate the same as i have i.e. none of the calculated measured like gross profit show up. Not entirely clear why this would be, but the intermediate calculations don’t have any direct reference to the header table. I haven’t got round to seeing if altering the measure makes any difference. for example make GP Act=CALCULATE ( [Actual Amt], Headers[Header] = “Revenue” )-CALCULATE ( [Actual Amt], Headers[Header] = “Cost of sales” )

    1. Working through using this approach myself today and it reminded me to respond to your comment because I had today (and often have) the same problem you are. The solution you cite above has definitely been the answer to “disappearing” measures. Also, my “show detail” logic has been off-point. Another issue, which was the cause of my problem today, is that I repurposed a measure and it wasn’t written correctly; rather silly, but sometimes the cause. Hopefully one of those works for you.

    1. Thank you! Unfortunately, the file posted above is all the support I have for this post. Creating a model-able structure from a trial balance would be another post (or 10). 🙂

  18. For what its worth I have tried a few experiments in Power BI as this is really frustrating.

    I set up my Header table like this

    Budget L1 ID Budget L1 Summary Show Detail
    1 Revenue 1
    2 Marketing 1
    3 Total Staff Costs 1 1
    4 Fixed Overheads 1 1
    5 Variable Overheads 1 1
    6 Total Overheads 1
    7 EBITDA 1
    8 Depreciation & Amortisation 1 1
    9 Net Contribution 1
    10 Exceptional 1 1
    11 Net Profit 1
    16 Balance Sheet 1

    My IsSubtotal measure is

    IS Subtotal Act =
    IF (
    COUNTROWS ( VALUES ( Budget_L1[Budget L1] ) ) = 1,
    SWITCH (
    VALUES ( Budget_L1[Budget L1 ID] ),
    1, [Total GP test],
    2, [Total Marketing],
    3, [Total Staff Costs],
    4, [Total Fixed Overheads],
    5, [Total Variable Overheads],
    6, [Total Overheads],
    7, [EBITDA],
    8, [Total Depreciation & Amortisation],
    9, [Net Contribution],
    10, [Total Exceptionals],
    11, [Net Profit],
    BLANK ()

    where Total Fixed Overheads = CALCULATE([Total Actual],Budget_L1[Budget L1]=”Fixed Overheads”)

    and Total Overheads= [Total Fixed overheads]+[Total Variable Overheads]

    as reported this gives data in the power bi report for the components, but no entry for the total one that sums the 2 measures.

    I then tried replacing the Total Overheads with CALCULATE([Total Actual],Budget_L1[Budget L1]=”Fixed Overheads”)+CALCULATE([Total Actual],Budget_L1[Budget L1]=”Variable Overheads”). This made no difference

    I then tried changing the formula for Total Fixed Overheads = CALCULATE([Total Actual],Budget_L1[Budget L1]=”Variable Overheads”). I thoguht I woudl just get a duplicate value for both fixed and variable overheads in the power BI report, but actually the Fixed one just disappeared.

    I don’t really understand the code, but seems in Power BI it is critical that the formula refers to the Budget L1 tha t is in the head table and if they aren’t the same you get no result. Is this because of the COUNTROWS bit?


  19. Hi,

    I am using this model for my organization and saved me lots of time. thanks a lot.

    I am trying add measure for %age to Revenue, but returns blank for subheader & account name level. tried the following
    %age to Rev:=DIVIDE([Actual Total],[Rev Act])

  20. Its a great post. helped me a lot in my work as finance analyst
    Trying to get %age of Revenue in the model, but not giving results for sub header and account name.
    %age of Rev:=DIVIDE([Actual Total],[Rev Act],0)

    1. I’m glad you enjoyed the post and that it’s helpful! Without opening the file and playing with it, did you put this metric on in the headers table and define it as you did gross profit (or one of the other calculations)? If so, in the example, we explicitly defined those to be shown with no drill-down. You might first play with the “show detail” and see if that works.

  21. This article has been a great help for me. Thank you very much for the detailed explanation! Because Excel was no option I was looking for a way to implement it in Power BI and in the end found a way to do it.
    I made the relationship between the header and the accounts table inactive. For the calculated fields like Gross Profit I used INTERSECT to set up the relationship between accounts and header.

    GP 1 = IF(ISFILTERED(Subheader[Subheader]),BLANK(),CALCULATE([GL Amt 1],INTERSECT(ALL(‘General Ledger'[GP]),VALUES(Headers[Header])))*-1)

    Finally I created 1 “master measure” that I use in the matrix.

    Master Measure = IF(HASONEVALUE(Headers[Header]),SWITCH(VALUES(Headers[Header]),”Gross Profit”,[GP 1],”Revenue”,[Rev Act 1]*-1 ,”Other Income & Expense”,[Rev Act 1]*-1 ,”Operating Income”,[Operating Income 1],”Net Income”,[Net Income 1], [Rev Act 1]))

    You can access the file by following link:

      1. I found a better solution for Power BI… It’s actually quite simple. The calculated members (gross profit, etc.) can be calculated as a running total. E.g. you have revenue and COGS. The gross profit is than the sum of revenue and COGS, so basically the running total of those 2. To only sum up revenue and COGS you give IDs to the accounts (revenue = 1, COGS = 2, gross profit = 3) and in your DAX expression you sum up all the rows where the ID is smaller than the ID of the current row:
        Running Total (PL) = IF(HASONEVALUE(Header[Header]),CALCULATE([GL Amount],FILTER(ALL(Header),Header[Header Id]<=MAX(Header[Header Id]))))

        We can than put this measure in a SWITCH function together with the sum of gl amount:
        GL Amount (PL) = IF(HASONEVALUE(Header[Header]),SWITCH(VALUES(Header[Show Detail]), 0 ,[Running Total (PL)], 1 ,[GL Amount]))

        The 0 and 1 come from the "Show detail" column in the header table (show detail = 1). If we do not want to show details (like with gross profit, net profit, etc.) we use the running total and otherwise we just use a normal sum.

        1. Hi Steven. Could you put up a link to the new version as not sure i follow 100%, but get the jist of your solution. seems you may have cracked it. Cheers Mike

  22. Hi Matt!

    Thank you for providing this tip on creating an income statement within PowerPivot. I am fairly new to this technology – but learning!

    I have converted your file and have all the DAX formulas working correctly with some of my sample data. The challenge I am having, is getting the Header sequence within the pivot table to sequence in a proper order. The pivot table result is in alphabetic order. However, within pivot table options, I do have the “Sort in data source order” toggled – and I have confirmed the header table is in sequence.

    Without seeing the file – do you have any quick thougths on things I should try!

    Again, thank you! I see so much potential with these Power Tools, I just need to keep going down the learning path!

      1. Hi Matt, sorry – I failed to see your response – but I am still struggling with this. The challenge I am having is finding the sort dialog option as you defined in artical. Your screenshot indicates the option to sort a certna column by another column – in your example Sort Header column by the HeaderID column. I am not finding this dialog box – the only sort options I am finding are the traditional A-Z, Z-A, or by cell color, etc

        Is this sort dialog box within the data model table view?

        I am using office 365 and should have most recent updates.

        Thank you – and looking forward to attending your upcoming training in Chicago. I need it:)

  23. Great job !!! Thanks.
    One comment: I tried to play with your model in Power BI Desktop by importing it.

    For your measures to work, you need to add an All(Headers[HeaderID]) to your calculate statements for your Sum Gl Amt measure:
    Sum GL Amt = Calculate ( SUM(‘General Ledger'[Amount]) , All(Headers [HeaderID] ) ).

    This is because of the ‘sort by other column feature’. More explanation from our Guru Marco Russo here: and here

    You didn’t have this issue in Power Pivot for Excel because MDX queries are sent in pivot tables.

    1. Tristan, thanks for this. I think it answers a lot of previously asked questions about the PBIX equivalent. I’m so stuck in my Excel ways and am a big fan of catching it up to PBI Desktop.

  24. Hi Steven. As far as i can see what you have done is for any intermediate calculation like Gross Profit you have labelled every row in the GL whether it contributes to gross profit. Then the same for operating expenses etc. So if i wanted an EBITDA number i would add an extra calculated column labelling every GL entry that makes up EBIDTA.

    While i can see it works it is far less elegant than the original Excel version. such a pity the original idea just doesn’t seem to work in Power BI. as this saves created multiple extra column in the GL table.

    Has anyone else found a Power BI solution that allows one to use measures for EBITDA that are based on other measures e.g. GP-expenses-marketing etc.

    Tristan were your comments directed at a Power BI solution or an Excel solution? I tried adding the clause you suggested in Power BI, but still don’t get any of the intermediate calculations.

    would love to get this to work in power BI without the extra columns of Steven’s solution.

  25. Indeed it not as elegant since you have to add a column for every intermediate calculation but performance wise I am satisfied with the solution. I implemented this in a production environment with couple of million of rows and it works smoothly. But would also love to hear a more elegant solution 🙂

  26. I’m working with approximately 8 million records on my P&L statement fact table. I develop the model in Power BI, publish to Power BI, but the end user connects via Excel (since this works best in Excel pivot table). The downside is performance is terrible and as a result, this is not in a production environment. I’ve got a finished model that’s working but not an efficient way to deliver it. I’m still waiting for that elegant Power BI solution. 🙂

  27. I am not sure what have I done wrong. I have really spend a lot of time looking at relationships and measure and I am not able to get Gross Profit or EBITDA measures to show through SWITCH. Would really appreciate your help!!! I have a table named “CM” with general ledger data (Rev, COGS and Opex data) that I pulled from SQL into Power BI desktop. Then, I created an “Accounts” Table and build a relationship between CM table and Accounts table with GL numbers. I also have a AccType column in my Account table showing Rev, COGS, and Opex for each of the Account numbers. Then, I created an AccType table that has Rev, COCS, GP, Opex, and EBITDA values in AccType column. Rev, COGS and Opex values are displayed correctly but GP and EBITDA does not show. here are my measures:
    Amount = SUM(CM[AmountCompanyCurrency]) ;
    Rev = CALCULATE([Amount], AccType[AccType] = “Rev”) ;
    COGS = CALCULATE([Amount], AccType[AccType] = “COGS”) ;
    Gross Profit = [Rev]+[COGS];

  28. Hi,

    Rev, COGS and Opex show correct values, but Gross Profit and EBITDA show blanks for me next to row headings from Header table. I spend a lot of time to make it work, but I failed. I will appreciate your help me with “Is SubTotal Act” Measure to show values for Gross Profit and EBITDA. I have GL data for Revenue, COGS and Opex in “CM” table in Power BI. Then I created a relationship between “CM” table and “Accounts” table on Account #. My accounts table has AccType column that list, Rev, COGS and OPEX for each account. It is related with AccType Table which has Rev, COGS, GP, Opex, and EBITDA (like your headers table). Then I created following measures:
    a) Amount = SUM(CM[AmountCompanyCurrency])
    b) Rev = CALCULATE([Amount], AccType[AccType] = “Rev”)
    c) COGS = CALCULATE([Amount], AccType[AccType] = “COGS”)
    d) Gross Profit = [Rev]+[COGS]
    e) Opex = CALCULATE([Amount], AccType[AccType] = “Opex”)
    f) EBITDA = [Gross Profit]+[Opex]
    g) Is SubTotal Act= IF(
    COUNTROWS(VALUES(AccType[AccType])) =1,
    VALUES(AccType[AccType ID]),
    1, [Rev],
    2, [COGS],
    3, [Gross Profit],
    4, [Opex],
    5, [EBITDA],
    “issue with if”)

    Thank you 🙂

  29. Is the VarDisplay working as intended. It looks like you have cost of sales, operating expenses, other inc, etc with blank values yet they are still populating variance $’s and %’s in your Final Pivot table results. Is the 1 or blank actually doing anything. I was having similar issues with the show detail. It hides detail for the GP & OpIncome, but changing the 1’s to blank on the other rows does not hide detail.

  30. Hi Matthew, first of all thanks for the walkthrough.

    Regarding you model, shouldn’t you change the measure “NI Act”
    NI Act:=[GP Act]-[OI&E Act]-[D&A Act]-[II&E Act]-[Taxes Act]
    NI Act:=[OI Act]-[OI&E Act]-[D&A Act]-[II&E Act]-[Taxes Act]


    Additionally in trying to create the same in PowerBI – not directly linked to this – I’ve found that for Gross Profit, Operating Income & Net Income (The aggregates ) , I’m require to state the measures as CALCULATE( Y – X , ALL(Headers) ) in order to trigger the measure inside the SWITCH statement – not completely sure why.

  31. Thank you Jesper! I too was having the issue where my calculated totals would not appear in the pivot table. I’m using Excel PowerPivot, not PowerBI. Your CALCULATE( Y – X , ALL(Headers) ) syntax solved this problem perfectly!

Leave a Reply

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