skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


 
SUMX of IF Used to Make Grand Totals Add Up in Power Pivot DAX

In This Case, Getting the Grand Total Correct for Each Row Required SUMX

It’s that time of year again…

…when my love of spreadsheets actually translates into a love of sports.  Yes, it’s Compulsive Data Crunching Disease season, AKA Fantasy Football Season.

Fantasy football is a game in which the contestants assemble “portfolios” of NFL players in the same manner that you might build a portfolio of stocks and bonds.  Then your portfolio (we call it a “team”) performs well if the real-life NFL players perform well, and poorly if not.  The one difference between this and the stock market is that no two “portfolios” can contain the same NFL player – so if I get Peyton Manning, the other contestants in my league (typically 8-12 people) cannot have him.

I’m participating in a new form of league this year, one in which the contestants get to keep some of the players from prior years.  (In most fantasy football leagues, you start each year from a clean slate).

We’re going to be picking our players this weekend at an “auction” or “draft,” and naturally, I want to scout my opponents ahead of time.  Muhaha.

So, what do my opponents need?

A valid portfolio consists of:

 

  1. 1 real-world NFL quarterback (QB)
  2. 2 running backs (RB)
  3. 2 wide receivers (WR)
  4. 1 tight end (TE)
  5. 2 “Flex” players (can be RB or WR or TE)

So, given this table:

image

This Table Reflects the Existing Portfolios –  My Team is Ebenezer
AJ Green, a WR, is Predicted to be Very Good This Year, So I Marked Him as Good=1
Andy Dalton, a QB, is Expected to be Mediocre, So Good=0

I want to tally up how much each team needs to “acquire” in this weekend’s auction event.

You still with me? I hope so, because now is where the DAX starts.

A few quick measures and we have:

image

Pretty Straightforward Start (Formulas Below)
But I Don’t Like That -1

[Starters]:=

  SUM(Rosters[Good])

[RB Starters]:=

  CALCULATE([Starters], Rosters[Position]=”RB”)

[RB Need]:=

  2-[RB Starters]

Why “two minus?”  Because the league rules say we need 2 RB’s, that’s all.

Negative Need Does Not Make Sense, so We Add an IF()

That last measure, [RB Need], I don’t like.  Once a team has 2 of any position, they are “full” and don’t need any more.  Yes, they can exceed that, and stash the extra players in reserve, but for my purposes, you cannot “need” less than zero.  No negative numbers allowed, please.

So we modify the measure to be…

[RB Need]:=

  IF([RB Starters]>2,0,2-[RB Starters])

Perfect.  If you already have  more than 2, you’re at 0 need:

Using IF in Power Pivot DAX to Round Negative Numbers to Zero

No More Negative Value for Need Smile

But Now the Grand Total is Wrong!

Using IF in Power Pivot DAX to Round Negative Numbers to Zero - Bad Total as a Result

The IF Returns 0 for the Grand Total, Because [RB Starters] is 13 in that Context

Ah, so now we change the measure to:

[RB Need]:=

  SUMX(VALUES(Rosters[Team]),
       IF([RB Starters]>2,0,2-[RB Starters])
      )

Which is just the original IF “wrapped” in a SUMX(VALUES()).  For more on SUMX, see the 5-Point Palm Exploding Function Technique.  For more, on VALUES, see this recent post.

And voila, all problems fixed:

SUMX of IF Used to Make Grand Totals Add Up in Power Pivot DAX

Everything is Now “Happy”

A Few Points of Perspective

1) I always struggle to find good examples of using SUMX to force totals to add up correctly.  Usually the examples I find when I am on the spot feel kinda…  dirty.  Like we are cooking the books or something.  But there are MANY legitimate needs for it – needs that are always super specific to the domain at hand, like the one above.

But I do think “need” is a pretty good example.  Any arithmetic operation where you want to round negative numbers up to zero will probably result in incorrect subtotals and grand totals.  So maybe I have found a good example here?

2) This is another example of using Power Pivot for a small, single table.  I didn’t use Power Pivot for this problem just because of habit.  I used it because it is BETTER – it took me less time than regular Excel and allows me the benefits of portable formulas (so I can rearrange the pivot, build alternate views, etc. without writing new formulas).

For another example of using Power Pivot on a small single table, see 1 Table, 50 Rows, 7 Columns: Analyze with PowerPivot? You Bet!

3) For some reason, SUMX of an IF just strikes me as damn cool.  I can’t put my finger on it, but it seems like the perfect blend of old and new, of simple and sophisticated.  Do you get that same vibe?

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 5 Comments
  1. I can give you an actual business example where we use SUMX and an IF.

    In the shipping world, people are concerned with the available supply of vessels in an area, which affects the price of securing vessels to transport your products. Tight supply, higher price – pretty straightforward. Being able to predict supply well means you can predict (and hedge) pricing better. So that’s what our PowerPivot model does.

    Where it gets tricky is “what constitutes available supply?”

    Some people just use a simple metric: “the number of vessels in or heading toward a certain area” (like the Gulf of Mexico.)

    Other people want to exclude vessels that are already in port in the Gulf, because they’re most likely loading and so aren’t part of the Gulf’s “available supply.”

    To make matters more tricky, not everyone wants to see supply at the Gulf level – maybe they just want East vs. West hemisphere, or North America vs. South America.

    So we have a raw measure VesselCount that’s just a COUNT(VesselID)

    We use an IF statement tied to a disconnected slicer to filter out rows in “loading ports” from VesselCount (this is VesselCountFiltered). But the way we determine whether a boat is in a “loading port” is using the VALUES(SubRegion), so if they don’t include SubRegion in their chart (like looking at East/West hemisphere only, for example) they just get the raw VesselCount.

    So instead we use a SUMX(VALUES(‘Geography'[SubRegion]), [VesselCountFiltered]) and then it filters out loading vessels no matter what hierarchy they use.

  2. Or for your example, if there were multiple leagues or divisions of teams, you could reuse your RB Need across them and get accurate counts, too, which is very nice in terms of user experience.

  3. I have a couple of workbooks where I need the subtotal & grand total to be calculated differently. For instance, I have these measures,

    SOLD_QTY:=CALCULATE(SUM(SOLD[SOLD]))

    TOTAL_SOLD:=CALCULATE([SOLD_QTY], ALLSELECTED(SOLD))

    TOTAL_SOLD_2:=IF(COUNTROWS(VALUES(SOLD[SOLD]))>0,[TOTAL_SOLD],BLANK())

    Normalized%:=[SOLD_QTY]/[TOTAL_SOLD_2]

    ORDER_QTY_TEMP:=CALCULATE(ROUND([OrderQuantityMax]*[Normalized%],0))

    The problem with [ORDER_QTY_TEMP] is that that subtotals (I have two columns size and subsize), and the grand total does not equal the individual value of each size & subsize due to rounding,

    I came up with the following, god knows how I figured this out but it works,

    SCALE_QTY:=IF( COUNTROWS( VALUES( SOLD ) ) > 0 ,
    IF( HASONEVALUE( SIZE[SUBSIZE] ) ,
    [ORDER_QTY_TEMP],
    IF( HASONEVALUE( SIZE[SIZE] ),
    SUMX(
    ADDCOLUMNS(
    SUMMARIZE(
    FILTERS( SIZE[SUBSIZE] ),
    SIZE[SUBSIZE]
    ),
    “MEASURE_SUBSIZE_TOTAL”,[ORDER_QTY_TEMP]
    ),
    [MEASURE_SUBSIZE_TOTAL]
    ),
    SUMX(
    ADDCOLUMNS(
    SUMMARIZE(
    CROSSJOIN(
    FILTERS( SIZE[SIZE] ),
    FILTERS( SIZE[SUBSIZE] )
    ),
    SIZE[SIZE],
    SIZE[SUBSIZE]
    ),
    “MEASURE_SIZE_TOTAL”,[ORDER_QTY_TEMP]
    ),
    [MEASURE_SIZE_TOTAL]
    )
    )
    )
    )

    however I want to add another column at this point (for color) but the way i’m calculating the above formula will make it too long, is there another easier way do calculate these kind of subtotal ans totals?

  4. I’ve used a similar construct for two different challenges recently.

    The first is counting working days with a number of different flags indicating non-working days (weekend and holiday in this case, but easily extensible). The pattern is this:
    SUMX( FILTER( ), IF( DateTable[WeekendFlag] = 0 && DateTable[HolidayFlag] = 0, 1, 0) ). This gives us a total of working days. You can also use some of the built-in time intelligence functions in place of the FILTER() depending on your need (DATESMTD/DATESYTD work well here).

    Another challenge was one on the Technet forums regarding a count of customers who bought an arbitrary basket of items (determined by a slicer selection). The gist of it is 3 measures:
    – A distinct count of items selected
    – An IF() function returning true if the distinct count of items in the fact table matches that of the first measure, populating 1 if true, else 0
    – A SUMX() over VALUES( table[Customer] ), with the IF() function passed as the second argument
    http://social.technet.microsoft.com/Forums/en-US/521996d5-92b8-412b-a89f-3f3e4443c6f0/basket-analysis-customer-buying-all-the-filtered-products?forum=sqlkjpowerpivotforexcel

Leave a Comment or Question