Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Pretty Nifty PivotTable

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

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:

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

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

image

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

CALCULATE(
           IF(
               HASONEVALUE(PlayersTable[FullName]),
               VALUES(PlayersTable[FullName]),
               COUNTROWS(VALUES(PlayersTable[FullName]))
                    & ” 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…

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

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:

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Repeat that 3-measure process for the other base measures [Rush TD] and [Rec TD] to yield:

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Why is this better than the older technique?

Three reasons:

  1. The formula is slightly simpler
  2. The formula runs faster than the older method
  3. 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.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has One Comment

Leave a Comment or Question