The FAQ is now accepting applications!

January 8, 2010

YoungGuns

“I’ll make ya famous.”

-William H. Bonney

When I first announced the FAQ, I mistakenly believed that I had a limited number of user accounts to give out.  Turns out, I have unlimited accounts, and the only limitation is on amount of content.  Right now that limit is 200 MB.

I’m pretty sure 200 MB is gonna have us covered :)

As you can see here, we’ve already got a number of excellent moderators for the FAQ (where “moderators” is a fancy word for “people who have write access.”)  I’m happy to add some more.

Moderator responsibilities are pretty light – you just have to be willing to contribute FAQ items from time to time.

If you’d like to be considered for this role, drop me an email or tweet (contact info at the bio link at the top of the page).  I don’t promise to take everyone who contacts me, but I will consider everyone.

Some guidelines:

  1. You do NOT have to be a Microsoft employee.  This is a community effort.
  2. You DO have to know what you are talking about.  You’ve spent time with PowerPivot already.  Maybe you’ve blogged about it, maybe you haven’t.  But you’ve done something you can tell me about.
  3. You DO have to post at least occasionally.  Over time I will trim the moderators list and clean out inactives.
  4. You DO have to be friendly, courteous, and responsible at all times.

Tobey-Maguire-Spider-Man“With great power comes great responsibility”

-Peter Parker

Basically I reserve the right to prune the list of moderators at any time and for any reason.  I won’t be a jerk about it of course (I, too, will follow rule #4), but if I need to tweak things I will.  Let’s consider this an experiment :)

I should also add that if you cease being a moderator for any reason, the items you contributed will stay on the site for the benefit of the community (but will remain attributed to you).

I don’t mean to sound harsh – what limited experience I have with this kind of thing tells me that clear expectations up front actually leads to a friendlier vibe all around.  No surprises = good.

With that out of the way, remember, we are here to help each other, and to enjoy the process.  Those are the goals.

Looking forward to hearing from you,
-Rob

3 Comments | Hello World / Administrivia, PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

Touchdown Problem Solved, and COUNTROWS

January 8, 2010

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].

PowerPivot PtsScoredOnPlay Calc ColumnA 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).

PowerPivot RushTD Measure Priest Holmes PowerPivot RushTD ESPN Priest Holmes

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

1 Comment | DAX, The Great Football Project | Tagged: | Permalink
Posted by powerpivotpro