First of all, apologies for being so late.  I feel like a total zombie, we got to our house in Ohio 22 hours after we arrived at the airport in Tel Aviv.

Second, how cool is this pivot???

Pivot that tells me my best selling product, using PowerPivot

Yes, This is a Pivot with Two Measures!

 

In last Thursday’s post I showed one way to display the top-selling date, or “date on which we had maximum sales” as a measure in a pivot:

Best Selling Date in PowerPivot

And the formula for [Date of Max Unit Sales] was:

=IF(ISBLANK([Sales Units on Max Day]),BLANK(),
   CALCULATE(FIRSTDATE(Calendar[Date]),
      FILTER(VALUES(Calendar[Date]),
         [Sales Units]=
         CALCULATE([Sales Units on Max Day],
            VALUES(Calendar[Date])
         )
      )
   )
)

But What if You’re Looking for the Best Selling Product?

In the formula above, note the highlighted use of the FIRSTDATE function.  That works great when I am looking for a top-selling date, as I was in the last post.

But what if I want to find something that isn’t a date?  What if I want to know something different, like the top-selling product?

Well, if I want to return a numeric/integer product ID, I could always substitute MIN or MAX for FIRSTDATE.  But what if it’s the product name I want to see, which is far more useful anyway?  There’s no MIN/MAX style function for text, sadly, so I have to get creative.

Use VALUES() Instead?

Well the VALUES() function will return text as long as my FILTER function returns only a single unique value.  If there are two or more products tied for the top-selling title, however, simply using the VALUES function will return an error.  More details here on that here.

Detecting the case where the FILTER returns more than one row seems awkward however, at least to my jetlagged brain.  So I’m gonna go a different route and use FIRSTNONBLANK() – a function I have been meaning to explore for awhile now anyway.

FIRSTNONBLANK (and LASTNONBLANK)

These functions strike me as having a million uses – the sort of thing where I have yet to grasp their full potential.  For them to make their “debut” here 2.5 years after the blog’s inception seems inappropriate, but hey, better late than never.

The syntax of “FNB” is quite simple:

FIRSTNONBLANK(Column, Measure Expression)

It’s actually a lot like SUMX in that it’s a “looping” function – it loops through all the values of Column, evaluates Expression each time, and returns the first value of Column for which Expression returns something other than Blank.

Let’s cut to the chase and try it out on Products:

[Name of Best Selling Product] =

FIRSTNONBLANK(Products[Name],
   IF([Sales Units] =
      CALCULATE(
         [Max Single Product Sales Units],
         VALUES(Products[Name])
      ),
      1,
      BLANK()
   )
)

Where everything in Green is the Measure Expression.  In this case I used an IF() that returns 1 when the current product’s [Sales Units] value is equal to the value of [Sales Units] for the best-selling product.  The latter is determined by my [Max Single Product Sales Units] measure, which is a MAXX measure just like how I did it in the previous post:

[Max Single Product Sales Units] =

MAXX(VALUES(Products[Name]), [Sales Units])

This all works great.  But just like in the last post, I have to add an IF() “wrapper” to prevent the measure from returning product names in cases where we sold nothing:

IF(ISBLANK([Sales Units]), BLANK(),
   FIRSTNONBLANK(DimProduct[EnglishProductName], IF([Sales
   Units] = CALCULATE([Max Single Product Sales Units], VALUES
   (DimProduct[EnglishProductName])), 1, BLANK()))

)

Where the orange text is the new wrapper and the grey is the original measure formula from above.

Flexible, Portable!

Like all good PowerPivot measures, this formula works in a lot of different contexts.  Best-selling product per year-month is cool, but we can also see it per city by changing the field on rows:

image

Just change the field on rows, formula still works!

What about ties?

OK, what about the case where two or more products tie for best selling?

Hmm, well, I must say I don’t have a perfect solution for this.  Here’s one partial solution though:

I created a second version of my original measure, but replaced FIRSTNONBLANK with LASTNONBLANK:

[Name of Best Selling Product LNB Version]=

IF(ISBLANK([Sales Units]), BLANK(),LASTNONBLANK(DimProduct[EnglishProductName], IF([Sales Units] = CALCULATE([Max Single Product Sales Units], VALUES(DimProduct[EnglishProductName])), 1, BLANK())))

And then a third measure which compares the original to the new version:

[Best Selling Product Ties Detected] =

IF([Best Selling Product]=[Best Selling Product LNB Version],
   [Best Selling Product],
   “Multiple Products”
)

In theory that should work – if there’s a tie, the first and last products will be different.  But for the life of me, I cannot get this sample data set to yield a result of “Multiple Products” anywhere, no matter what I try.  So I am suspicious that maybe the FNB and LNB functions don’t respect “first” and “last” quite so faithfully.  Let me know if your results differ.

There are other problems here too of course.  It would be nice to know both product names in the event of a tie, in which case the measure could concatenate their names rather than returning “Multiple Products.”  But if there were three products tied, I don’t even know how to get the name of the “middle” product.  Perhaps in PowerPivot V2 I’ll discover a way.

More Efficient Way in PowerPivot V2?

Speaking of V2, Gerhard posted a comment on the last post that is worth calling out:

http://powerpivotpro.com/2012/06/finding-the-top-selling-date-product-etc/#comment-5504 

But given my limited experience with V2 to date, that’s over my head for the moment Smile

Your Uses of FNB/LNB

I’m curious – are you using FIRSTNONBLANK() and LASTNONBLANK() today?  And if so, I bet you are using it in a very different manner.  Send me an email or leave a comment, I would very much like to know where and how you are using these functions.

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

  1. Here is what I came up with using PowerPivot v2:

    TopSellingProduct3:=IF(NOT(ISBLANK([OQ])),
    IFERROR(
    CALCULATE(
    VALUES(DimProduct[EnglishProductName]),
    TOPN(
    1,
    VALUES(DimProduct[EnglishProductName]),
    [OQ])),
    “Multiple Products”))

    [OQ] is a simple SUM over [Order Quantity]-column
    IfError is need because TOPN may return multiple values if there are ties

  2. Hi. What about getting the 2nd ranked and subsequent ranked products? I tried using a calc in the format IF(ISBLANK([HoldingsAmountSum]), BLANK(),
    FIRSTNONBLANK(DimInvestor[InvestorName], IF([InvestorRankByHolding] = 1, 1, BLANK()))
    ) – with the ranking measure as the filter – but it performs pretty horribly

  3. Any idea how to get the previous day with a nonblank value, since the calculation below still returns the previous day’s value if it’s blank? I’m stumped.

    Previous Net Funded Volume:=CALCULATE([Net Funded Volume],LASTNONBLANK(PREVIOUSDAY(DimDate[FullDate]),[Net Funded Volume]))

    Thanks,

    Ben

  4. HI BOB; I WONDER IF THERE IS A FUNCION AS SUMX BUT INSTEAD OF ADDING, SUBSTRACT VALUES FROM NEXT OR PREVIUS CELL IN TO THE SAME COLUMN

    I NEED THE FORMULA TO OBTAINS THE VALUE FOR [DURACIÓN] AS I SHOW IT AT THE FOLLOWING EXAMPLE.

    DATE PERSONA SECCIÓN DURACIÓN

    13/04/2016 5:44 PEPE PEDIDOS
    13/04/2016 5:44 PACO TAR.VAR
    13/04/2016 5:44 JUAN PEDIDOS 3:12:39 ( =A11-A4)
    13/04/2016 5:44 LOLA PEDIDOS
    13/04/2016 5:44 PEDRO REC
    13/04/2016 5:44 SANDRA PEDIDOS
    13/04/2016 5:44 MARIA PEDIDOS
    13/04/2016 6:51 PEPE EXP
    13/04/2016 7:54 PACO TIENDA
    13/04/2016 8:57 JUAN INCI 2:00:51 (=A18-A11)
    13/04/2016 9:59 LOLA TAR.VAR
    13/04/2016 10:02 PEDRO TIENDA
    13/04/2016 8:55 SANDRA REC
    13/04/2016 8:57 MARIA REC
    13/04/2016 9:49 PEPE TIENDA
    13/04/2016 9:54 PACO PEDIDOS
    13/04/2016 10:57 JUAN REC 0:08:00 (=A22-A18)
    13/04/2016 10:59 LOLA PEDIDOS
    13/04/2016 11:01 PEPE ALM.VERT
    13/04/2016 11:03 PACO REC
    13/04/2016 11:05 JUAN PEDIDOS 0:56:44 (=A29-A22)
    13/04/2016 11:07 LOLA ALM.VERT
    13/04/2016 11:09 PEDRO PEDIDOS
    13/04/2016 11:11 SANDRA PEDIDOS
    13/04/2016 12:03 MARIA SALIDA
    13/04/2016 12:02 PEPE SALIDA
    13/04/2016 12:02 PACO SALIDA
    13/04/2016 12:02 JUAN SALIDA 12:02:37 (=A29)
    13/04/2016 12:02 LOLA SALIDA
    13/04/2016 12:02 PEDRO SALIDA
    13/04/2016 12:02 SANDRA SALIDA

    REGARDS AND TKS VERY MUCH ON ADVANCE

  5. How could I realize the top-selling “sub” product using in addition to top-selling product e.g. top procuct = “car”, top selling sub product of top selling product “car” = “SUV” ?

  6. Hey Rob, love your blog and your book!
    Not sure if it was intentional, but in the “green” formula above you use “Products[Name]” whereas in the “grey” formula and later you use “DimProduct[EnglishProductName]”. Not a big deal, but had me slightly confused at first (though I am easily confused…).

Leave a Comment or Question