PowerPivotPro

PowerPivotPro is Coming to Atlanta

March 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language
  • Taught by Ryan Sullivan – Principal Consultant
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms

MARCH 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop
  • Taught by Ryan Bergstrom – Principal Consultant and Trainer
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges
Atlanta Public Training Classes
PowerPivotPro Logo

 
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