Answered by:
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.
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 Valuespane > 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 RANKXfunction
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 PMAnswerer
All replies

you could simply use Excel Pivot Table Filtering
click on RowLabels > Value Filters > TOP 10...
Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.comWednesday, May 14, 2014 12:30 PMAnswerer 
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 Valuespane > 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 RANKXfunction
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 PMAnswerer 
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