Grabbing Rank 1 From Subcategories

[FightThePowerBI]

Hello!
I'm playing around with POWERBI and I ran into an issue that I've been stuck on. Below is the dummy data I'm using/
Stores

                                   
Store ID        BranchName    Region ID//The rest of the table has a store info     

1                     ME                     A

2                     DR                     A      

3                     QE                     B

4                     TQ                     A

5                     VE                      B       

6                     DQ                     B

SignUpSheet

Store ID          Signups

1                           5

2                           3

3                           4

4                           8

5                           2

6                           1

I've managed to make a filtered column for store rank that accurately ranks where each store stands as a whole. I've done the same for finding out where they stand as a region. 
Store RANK DAX = Store,[signsups] //sign ups has a small filter

What's different about the region ranking is that I need to use the Earlier function to have all stores in their regions ranked against each other. That's why I created it as a column and not as a measure since Earlier() doesn't work in a measure.  

Region Rank DAX = RANKX(
                                          FILTER(Store, Store[RegionID] = EARLIER (Store[RegionID])),
                                         [signsups])

Now lets say I want to grab the store that is ranked one overall. That's easy. I can create the following measure. This measure allows me to grab the store by it branch name.
#1 Store = Calculate(
                  FIRSTNONBLANK(Store[StoreID],0,

                   FILTER(Dimbranch, [Store Rank] = 1)

But how do I get the rank 1 store in a specific region? I am using a slicer where I can select stores where a card shows the #1 store overall and #1 store in its own region. BUT I will not be using a region slicer so that I can only see that region so it can't be separated that way. 

So I will need to pick a store via a slicer and it will show me the #1 store overall by its branch name (This does mean the store won't change unless the data changes) and then the #1 Store in its own region by its branch name(This will change if the current picked store has a different region that previously picked store since the top store in this region will be different than the other region).  If need more info, please let me know.

[FightThePowerBI]

Sorry for the late response but this is a step in the right direction. One thing I noticed is if I try to use a Store ID slicer, then the store rankings break. Is it something that will always happen?

[v-kelly-msft]

Hi  @FightThePowerBI ,

Do you mean something like this:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[FightThePowerBI]

As I continue to work on it I thought of an idea where I can Have each branch have a column which says which store ID is at the top of their region.  From there needs more work.

[v-kelly-msft]

Hi  @FightThePowerBI ,

If you need a dynamic ranking,you need to create measures instead.

Pls check my similar thread below:

https://community.powerbi.com/t5/Power-Query/Can-I-get-dynamic-Unique-Rank-on-the-basis-of-3-measure...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[FightThePowerBI]

The issue with using that method is that my store ranking within the region won't be correct since I won't be able to use the earlier function. I also won't be able to create a region slicer and only a store slicer. 

[v-kelly-msft]

Hi  @FightThePowerBI ，

Using column, you will get a fixed ranking,so measure is  a better choice for you.

Could you provide a detailed sample data for me to test?Better with your expected output.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[FightThePowerBI]

Hello @v-kelly-msft 

Store Table

Signup Table

Results Table

Sorry for some reason it wont let me upload the file

[v-kelly-msft]

Hi  @FightThePowerBI ,

I could get the result of Store Rank and #1 Store as shown below:

But how to get Region rank and #1 Region store?What is the logic?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[FightThePowerBI]

The region rank shows the rank of the store within its OWN region. So if we look at stores with the ID of 1 and 2 we noticed that they both share the same region A. Between these two stores, storeID = 1 has more signs up in the region and thus is ranked 1 in the region. 

[v-kelly-msft · Accepted solution]

Hi @FightThePowerBI ,

First create a column in signup table:

_lookup = LOOKUPVALUE('Store Table'[Region ID],'Store Table'[Store ID],'Signup Table'[Store ID],blank())

Then create below measures:

Store Rank = RANKX(ALL('Signup Table'[Store ID]),CALCULATE(COUNT('Signup Table'[Store ID])),,DESC,Dense)

#1Store = 
var _storeid=CALCULATE(MAX('Signup Table'[Store ID]),FILTER(ALL('Signup Table'[Store ID]),'Signup Table'[Store Rank]=1))
Return
CALCULATE(MAX('Store Table'[Store Name]),FILTER(ALL('Store Table'),'Store Table'[Store ID]=_storeid))

Region Rank = RANKX(FILTER(ALL('Signup Table'),'Signup Table'[_lookup]=MAX('Signup Table'[_lookup])),'Signup Table'[count],,DESC,Dense)

#1 Region Store = 
var _storeID=CALCULATETABLE(VALUES('Signup Table'[Store ID]),FILTER(ALL('Signup Table'),'Signup Table'[Region Rank]=1))
Return
CALCULATE(MAX('Store Table'[Store Name]),FILTER(ALL('Signup Table'),'Signup Table'[Store ID] in _storeID&&'Signup Table'[_lookup]=MAX('Signup Table'[_lookup])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!