October 23, 2018 at 7:01 pm #11035
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.
Attachments:You must be logged in to view attached files.October 23, 2018 at 8:21 pm #11040
what version of Excel are you using?October 23, 2018 at 8:22 pm #11041
Hi, thank you for your reply. I’m using Excel 2013October 23, 2018 at 9:18 pm #11042
Store Rank Within Zone := IF ( HASONEVALUE ( Stores[StoreID] ), RANKX ( ALL ( Stores[Region], Stores[Store Name] ), [Units Sold] ) )October 23, 2018 at 9:43 pm #11043
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. 🙂October 23, 2018 at 9:47 pm #11044
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 🙂
You must be logged in to reply to this topic.