Pretty Nifty PivotTable
Same PivotTable, Diagrammed/Explained
Revisiting an Older Topic
About a year and a half ago I posted on this topic. At the time I was finding the top-selling date for a particular product:
From the June 2012 Post: Finding the Top Selling Date. Today’s Technique Can Also Be Used for That, and is Better Than The June 2012 Technique.
The Old Technique Used FILTER. This One Uses TOPN.
First, the simple measure that finds the largest single-player Pass TD total:
[League Leading Pass TD Amount]=
MAXX(PlayersTable, [Pass TD])
That steps through each row of the PlayersTable, evaluating the [Pass TD] measure at each step, and returns the single largest value it finds.
That’s the easiest part, and is no different from the older post.
Now, however, let’s find WHO had those 41 touchdown passes in 1999:
[Player with most Pass TD] =
& ” Players Tied”
TOPN(1, PlayersTable, [Pass TD])
Explaining the formula
Let’s start with the part in yellow. That’s pretty simple – go and find the single row from the PlayersTable with the highest value of [Pass TD].
And since TOPN is being used as a <filter> input to CALCULATE, our IF() expression, that first part, is going to be “limited” to just that top row from PlayersTable.
IF() as 1st input to CALCULATE? Weird, but handles ties wonderfully.
But why is there an IF() for the measure expression input to CALCULATE?
The thing is, if there are two players in PlayersTable that BOTH have the top amount of touchdowns, TOPN(1,…) will return both players!
So our IF(HASONEVALUE(…)) test “detects” the case where we have ties! If we have just one value, then there are not any ties, so we just return the name of the player via VALUES(PlayersTable[FullName]).
If we have more than one, the second branch of the IF() is triggered, and we count the number of ties via COUNTROWS(VALUES()) and concatenate that number with the static text “ Players Tied” – note the leading space.
So we get…
Simple Concatenation To Combine Into One Measure
Then, if we want, we can write another measure that combines these two into one, for compactness:
[Pass TD Champ and Amount] =
[League Leading Pass TD] & “: “ & [Player with Most Pass TD]
Then remove the other measures from the pivot and get:
Repeat that 3-measure process for the other base measures [Rush TD] and [Rec TD] to yield:
Why is this better than the older technique?
- The formula is slightly simpler
- The formula runs faster than the older method
- This method handles ties
Back on the older post, Gerhard suggested a technique using SUMMARIZE. And that was also a LOT faster than the FILTER method. I wonder if this TOPN approach is just as fast as SUMMARIZE, or if there’s a difference. Hopefully he or the Italians will drop by and weigh in.