PowerPivotPro

PowerPivotPro is Coming to Atlanta

March 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language
  • Taught by Ryan Sullivan – Principal Consultant
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms

MARCH 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop
  • Taught by Ryan Bergstrom – Principal Consultant and Trainer
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges
Atlanta Public Training Classes
PowerPivotPro Logo

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” )

Measures

Measure

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 ()
    ),
    100
)

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


  Subscribe to PowerPivotPro!
X

Subscribe

Matthew Mowrey

Matt Mowrey is the Director of Finance & Analytics at a leading international development consulting services company. He manages FP&A, invoicing, 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 Cheboksary, Russia. 

This Post Has 45 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 https://www.youtube.com/watch?v=ojHZkWkEY7Q

        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 – https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/ – and another for best practices – https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/.

    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,
    Phillip

    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: http://www.linkedin.com/in/matthew-mowrey.

    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 (
      ISFILTERED(Headers[Summary])=1
      && (
      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?

    Thanks!

    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.

Leave a Comment or Question