locked
How to do the ranking by writing a measure? RRS feed

  • 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 Lin Alex Tuesday, March 10, 2015 8:57 AM
    Tuesday, March 10, 2015 8:54 AM

Answers

  • 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 Michael Amadi Sunday, March 15, 2015 8:00 AM
    • Marked as answer by Michael Amadi Sunday, March 22, 2015 2:15 PM
    Friday, March 13, 2015 1:47 PM
    Answerer

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
    Answerer
  • 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])))

    described in: http://www.powerpivotblog.nl/where-your-calculated-field-is-executed-matters/


    Imke

    Wednesday, March 11, 2015 6:30 AM
    Answerer
  • 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 Michael Amadi Sunday, March 15, 2015 8:00 AM
    • Marked as answer by Michael Amadi Sunday, March 22, 2015 2:15 PM
    Friday, March 13, 2015 1:47 PM
    Answerer