# How to do the ranking by writing a measure?

• ### Question

• Hi, I'm trying to write a measure to show the ranking of each customer in the powerpivot pivot table. The rank is calculated based on rolling 12 months sales. I used RANKX but the result was inaccurate as shown below.

 AK_End_Customer_Name__rName Rolling12mSales Rank XX机场 \$3,809,824 1 XXX公司上海分公司 \$2,434,245 1 上海XXX大厦 \$1,103,973 2 上海XX管理有限公司 \$862,068 3 上海XXX公司 \$774,446 4 XXX南京分公司 \$773,532 1 XXXX服务有限 \$607,462 5 XXX广场 \$453,013 6 XX广场 \$420,585 7 XXX管理中心 \$376,319 8 XX管理有限公司 \$336,015 9 XXX儿科医院 \$326,686 10 上海XX服务有限公司 \$273,933 11 XXX中心 \$265,592 12 上海XX公司 \$220,987 2

My formula is =IF(NOT(ISBLANK([Rolling12mSales])),RANKX(ALL([EndCustomerName]),[Rolling12mSales]),BLANK())

Can someone help with this? Really appreciate it.

Alex

• Edited by Tuesday, March 10, 2015 8:57 AM
Tuesday, March 10, 2015 8:54 AM

• you may simply use built-in Pivot Table ranking as I described here:
https://social.msdn.microsoft.com/Forums/en-US/a0d37f68-bd40-43db-8f30-60964c3df10b/rankx?forum=sqlkjpowerpivotforexcel

hth,
-gerhard

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

• Proposed as answer by Sunday, March 15, 2015 8:00 AM
• Marked as answer by Sunday, March 22, 2015 2:15 PM
Friday, March 13, 2015 1:47 PM

### All replies

• Only thing that looks strang to me is the brackets [ ] around "EndCustomerName" try to remove them.

Would think that this would suit in a calculated column - why take it as a measure?

Imke

Tuesday, March 10, 2015 8:17 PM
• The field names are too long so I made them short as an example. I think the brakets are not the issue.

I only have a fact table with all information in it, and I tried to make the report dynamic. A measure is used cause I want to use it for other calculations such as TOPN or cumulative sales %.

Wednesday, March 11, 2015 2:09 AM
• Ah, so you might try this:

=RANKX(ALL([EndCustomerName]), CALCULATE(SUM([Rolling12mSales])))

Imke

Wednesday, March 11, 2015 6:30 AM
• I tried it, and it showed the error with the message "Column 'Rolling12mSales' in table 'Sales' cannot be found or may not be used in this expression.". :(
Wednesday, March 11, 2015 6:42 AM
• you may simply use built-in Pivot Table ranking as I described here:
https://social.msdn.microsoft.com/Forums/en-US/a0d37f68-bd40-43db-8f30-60964c3df10b/rankx?forum=sqlkjpowerpivotforexcel

hth,
-gerhard

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

• Proposed as answer by Sunday, March 15, 2015 8:00 AM
• Marked as answer by Sunday, March 22, 2015 2:15 PM
Friday, March 13, 2015 1:47 PM