PowerPivotPro

In the last post I was struggling with horribly-designed source tables.  To be honest, I never found a way to relate those two tables to each other, even through other intermediate tables that initially seemed promising.

But I DID find two columns in my Plays table itself that saved my bacon – [OffensiveTeamScoreAtPlayStart] and [OffensiveTeamScoreAtPlayEnd].

A simple calc column subtracting the two and now I have [PtsScoredOnPlay]:

  =[OffensiveTeamScoreAtPlayEnd]-[OffensiveTeamScoreAtPlayStart]

And that appears to give me what I want – see the picture at right.

(I was a little suspicious at first that the Offensive team could score 2 pts, since only defenses can score safeties, but then I realized these were 2-pt conversions by the offense – see how subtle “business rules” can be?)

OK, so with that calc column in hand, and my knowledge that all Touchdowns score 6 pts, I can go back to my PivotTable and add a measure:

   =CALCULATE(
         COUNTROWS(Plays),
         Plays[PlayTypeName]="RUN-Run",
         Plays[PrimaryRoleName]="Runner",
         Plays[PtsScoredOnPlay]=6
    )

Dissecting the Formula

I’ve broken the formula into separate lines above.  Let’s go line by line.

1. The first line is just the CALCULATE function.  I have previously described CALCULATE as a supercharged version of SUMIF.  That bears repeating.  Think “improved SUMIF.”
2. The second line is the expression to be aggregated.  You are not limited to SUM.  In fact you can place many custom expressions here.  In this case I am using the COUNTROWS function.  I will explain that below.
3. The remaining 3 lines are just conditional filters, like the “if” part of SUMIF, or more accurately, SUMIFS, since there are more than one.  Notice that the last one filters down to just plays that scored 6 pts, which are touchdowns.  The other two filters insure that this was a Rushing play, and the current player was indeed the one carrying the ball.

COUNTROWS is another good (new) friend

Longtime PivotTable users are familiar with a common trick.  When you want to count the number of rows from your source data that meet various criteria (in a PivotTable), you find a column in your data that is guaranteed to be unique, and then you add that field to your Values area, and change the “Summarize By” to count.

And if you didn’t have a column that contained unique values, well, you created one.

With COUNTROWS, you no longer have to do that.  COUNTROWS(<TableName>) works every time.

Tying it all together

OK, then, the formula above says:  “In my Plays table, count the number of rows that are rushing plays, where the current player is the one carrying the ball, that resulted in 6 pts.”

Does it work?  You bet.  PivotTable on left, ESPN on right.  (Priest Holmes, 2004).

OK, back to Sexy!

In the next post, I can now return to what I was originally trying to do, which was something semi-mind blowing…  if you’re an Excel geek anyway

Next Football Post:  Introducing the Anti-Pivot >>

Ok, in the last football post, I had written the following measure:

Which basically means, “sum up the [Yards] column but only those rows with [PlayTypeName] = “RUN-run” “

Now we get to find out if it works

I need some real-world data that I can validate against.  Out on NFL.com I can find historical player stats.  Let’s look at one of my favorite players from recent years, Priest Holmes.  He amassed a lot of Rushing Yards and should be a good sample.  Here are his statistics by Game for 2004:

OK, so let’s make a PivotTable that just shows Priest’s 2004 Rush Yards by Game, and compare that to the Rush Yards column from above:

                           My Pivot Table                                      From NFL.com

Bingo!

“You know sometimes I even impress myself?”

-Han Solo

An exact match!  That might seem a bit bland to you folks out there, but to me, it is VERY exciting.  I’ve got 40+ tables, a Plays table that is loaded with crazy complexity, no database training whatsoever, and yet…  I now have a measure that agrees, DOWN TO THE YARD, with NFL.com!

Wahoo!  In all honesty, when I started this project, I had no idea how far I could go.  I intentionally chose something that *might* defeat me.

I’m feeling pretty darn optimistic now, though

I’m suspicious, however…

I recall that my Plays table contains multiple rows per play, so that, for instance, it can capture data about the *defensive* players involved in a play:

Right there, a single play, three rows.  One for the runner himself, one for the player who tackled him, and another for the player who assisted the tackle.  That will come in handy when I get started looking at defensive stats.

But for now, I suspect that means defensive players are getting credited with rushing yards, too.  When I filter to Antoine Winfield, the Assister from above, my PivotTable confirms my suspicions:

What we are seeing there is the total net yards of running plays in which Antoine Walker was involved in tackling the runner.  I don’t want that.

But all I have to do, then, is add another clause to my CALCULATE function.

And now the pivot table shows:

Priest Holmes is unchanged.  Antoine Winfield now has no rushing yards.  Perfect!

CALCULATE is good.  CALCULATE is your friend.

“OK Rob, a whole post just so you can make ONE change??”

Yes.  I spent more time than usual on this.  Here’s why:  Everything above took me less than 10 minutes in real time.  It took far longer to capture the screen shots than simply to blaze through it.

But when I worked with a BI consultant, a few years back, the same exact iteration took about a week.

How do we account for the difference?  Is it because the consultant produced better results?  Was the Rushing Yards measure more accurate or robust than mine?  Was it somehow more formalized, more robust?  Or was the consultant not very good?

The answers are no, no, no, and an emphatic “no.”  (The consultant was fabulous, akin to godlike.  There’s that word again – “akin.”  Why do I keep saying that?)

The real difference, as I’ve said before, is that with PowerPivot, the “modeler” and the “business user” are the same person.  I’m the one writing the expressions, and the one who knows the most about what I want, because I know the “business” (football, in this case) inside and out.  Iteration in one person’s head is blazingly fast.

1. Finding the comparison stats on NFL.com – Only the business user knows where the best validation data sets are.
2. Creating the pivot for comparison purposes – even just choosing my player for comparison reflected business knowledge. 
3. Realizing that I likely was incorrectly counting defensive players in my measure – because I had access to the source tables and the “business rules” in my head, I spotted this problem before it ever made it into a report.

Seriously, this was like a trip down memory lane, on hyper fast forward.  I’d get a cube from the consultant, build some pivots, see that things were not accurately reflecting football rules, point out the problem, wait for the next version, repeat.

Not anymore

“Are you saying we don’t need BI Pros anymore?”

No, I am NOT saying that.  Even in this football example, I am cheating.  The original data from STATS arrived as a jumble of text files.  The schema was terrible.  Many of the required attributes (like the score of the game on a particular play) were completely missing.

There was a TON of work, done by the BI professional, to get from that horrible mess to the 40+ tables that I am working with now in PowerPivot.  And there is no way, no way at all, that I could do that myself – not back then and not today.

In terms that an MS BI Pro understands, the Integration Services work remains.  In fact, it becomes even more important, since you need to make the resulting schema not just work for people like you, but also for people like me

But the Analysis Services work – you can start sharing that with the Excel business users.

Next Football Post:  The Horrors of Bad Data Sources >>

“It’s been a long time since I rock-‘n-rolled…”

-Robert Plant

THE GREAT FOOTBALL PROJECT RETURNS!  Oh yes, it has been a long time indeed.

When I last worked on the football project, I had designed my first report and started the process of better understanding my source data, primarily using the =RELATED() function.  I mentioned a couple of times that the yardage totals being displayed were not yet trustworthy, and that they needed a lot of work before they were accurate.

Let’s get started on making them accurate, because, well, accurate is good.  We like accurate.

A Modest Proposal:  Rushing Yards First

Rather than boil the ocean, let’s start with the simplest type of play in football:  the running play.  Someone takes the football and just runs with it, and hopefully, makes positive progress down the field (measured in yards).  This is often called a “rushing” play, and it generates “rushing yards.”

So ultimately, I want a PivotTable that shows me a list of players, and their Rushing Yards in various situations.

My Source Table Looks Like WHAT??

So far I have just been adding the Yards field to my reports, yielding a Sum of Yards column:

But my source table (the Plays table) typically contains multiple records for a single play, with each record detailing a different player involved in the play, and their role.  Check THIS out:

Um…  yeah.  There are a multiple rows per play AND a bajillion types of plays.  And they are all mixed in there in one table.  Scanning the screenshot above and applying my knowledge of football, I can say that only ONE row should actually count toward Rushing Yards.

I believe the technical database schema term for this sort of thing is “icky.”  But I don’t think we can blame anyone for this.  Really, football is pretty complex – for a given event, the amount of data that we want to capture can vary tremendously, simply based on what type of play it was, and even based on what HAPPENED on the play.

That’s pretty complex.  But PowerPivot has a secret weapon for this sort of thing.

Und now’s the time on Sprockets when we Calculate!

In traditional Excel, I would solve this problem like this:  I’d create a new column in the Plays table, name it [Rushing Yards], and then use =IF() to give that column a value only when it indeed supposed to count as Rushing Yards.  Then I’d add the Sum of that column to my Pivot.

But as I repeat that process for every other type of yardage – passing, receiving, etc., I’m going to make an already unwieldy table even larger, which makes my life harder (more scrolling) AND increases file size.

In PowerPivot, I don’t have to do that.  I just go straight to the Pivot Table itself and add a new DAX measure:

Let’s zoom in on that formula:

That’s pretty simple.  The first param is the numerical quantity you are trying to measure – Sum of Yards in this case.  All subsequent parameters are filters/conditions that you want to apply while evaluating that quantity.  Which in this case, is Play Type = “RUN-run”

In the next post, I’ll tell you whether that works     And explain in more detail what’s going on here.  If my voice is feeling better, I will do it as a video.  But for now the quiet masses who prefer text and pics may rejoice