# Ranking outlet wise quantity sold

• ### 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.

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

Wednesday, May 14, 2014 2:11 AM

• 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 Thursday, May 15, 2014 5:45 AM
Wednesday, May 14, 2014 1:01 PM

### 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
• 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 Thursday, May 15, 2014 5:45 AM
Wednesday, May 14, 2014 1:01 PM
• 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