locked
Ranking outlet wise quantity sold RRS feed

  • Question

  • Hi,

    My objective is to show the Top 10 Outlets which I sell my goods to.  So this is what I did:

    1. I dragged Outlet ID to the Row labels
    2. I then computed [Quantity sold in 2013] using the following calculated Field formula

    =CALCULATE([Quantity sold],Calendar[Year]=2013)

    3. The [Quantity sold] Field has been computed as

    =SUM('Sales data'[Quantity (in Units)])/1000

    4. I then computed [Rank by Quantity in 2013] as

    =if(NOT(HASONEVALUE('Sales data'[Outlet Id])),BLANK(),RANKX(ALL('Sales data'[Outlet Id]),[Quantity sold],[Quantity sold in 2013]))

    5. The result is mostly correct except that the rank for the highest quantity sold is 2 (not 1).  Please see screenshot below.

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Wednesday, May 14, 2014 2:11 AM

Answers

  • you can also make your measure display as Rank instead of absolute value

    simply select your measure from the PivotTables Values-pane --> Value Field Settings --> set "Show values as" to "Rank Largest to Smallest"

    to make your DAX work correctly make sure to use the same measure as parameter 2 and 3 in your RANKX-function


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Ashish Mathur Thursday, May 15, 2014 5:45 AM
    Wednesday, May 14, 2014 1:01 PM
    Answerer

All replies

  • you could simply use Excel Pivot Table Filtering

    click on Row-Labels --> Value Filters --> TOP 10...


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, May 14, 2014 12:30 PM
    Answerer
  • Hi,

    Thank you for replying. Infact that is what i have already done. I also want to show a rank column in the Pivot Table. I want the rank for the first cell to be shown as 1 (not 2). I just want to know the mistake in my formula.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Wednesday, May 14, 2014 12:38 PM
  • you can also make your measure display as Rank instead of absolute value

    simply select your measure from the PivotTables Values-pane --> Value Field Settings --> set "Show values as" to "Rank Largest to Smallest"

    to make your DAX work correctly make sure to use the same measure as parameter 2 and 3 in your RANKX-function


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Ashish Mathur Thursday, May 15, 2014 5:45 AM
    Wednesday, May 14, 2014 1:01 PM
    Answerer
  • Hi,

    Thank you for replying.  Method 1 definitely works.  The formula based solution also works with the following modification

    =if(NOT(HASONEVALUE('Sales data'[Outlet Id])),BLANK(),RANKX(ALL('Sales data'[Outlet Id]),[Quantity sold in 2013]))


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, May 15, 2014 5:45 AM