Home Forums Power Pivot RANKX problems ranking Store within Area

Tagged: 

This topic contains 5 replies, has 2 voices, and was last updated by  Ste 5 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #11035

    Ste
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Hi,

    I’m having some difficulty using the RANKX function. I’m attempting to rank Stores within the Area but it’s not working when filtering by Region. I need the rank to remain the same when filtering by Region.

    I have attached a file where I have created a simple PowerPivot model.

    I’ve trawled through all of the forums, youtube, and powerpivotpro and no joy so far.

    Can someone please please help? It’s driving me crazy 🙂 I would really, really appreciate it.

    Thanks,

    Ste

    Attachments:
    You must be logged in to view attached files.
    #11040

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    what version of Excel are you using?

    #11041

    Ste
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Hi, thank you for your reply. I’m using Excel 2013

    #11042

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561
    Store Rank Within Zone :=
    IF (
        HASONEVALUE ( Stores[StoreID] ),
        RANKX ( ALL ( Stores[Region], Stores[Store Name] ), [Units Sold] )
    )
    #11043

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    The reason why the formula should work for you is due to your good model design (star schema, not snow-flaked) and cross-filtering available since region, store and zone are in the same table. 🙂

    #11044

    Ste
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Hi,

    That works a treat !!!! I was losing my mind trying to solve this, so thank you so much for your help. I really appreciate it.

    Also, thank you for your kind words regarding my model design 🙂

    Ste

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.