cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FightThePowerBI
Frequent Visitor

Grabbing Rank 1 From Subcategories

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.

1 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:

vkellymsft_0-1627291239533.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

View solution in original post

10 REPLIES 10
FightThePowerBI
Frequent Visitor

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?

Hi  @FightThePowerBI ,

 

Do you mean something like this:

vkellymsft_0-1627612532628.png

Best Regards,
Kelly

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

FightThePowerBI
Frequent Visitor

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.

 

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!

 

 

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. 

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!

Hello @v-kelly-msft 

 

Store TableFightThePowerBI_0-1626902901578.png

 

 

 

Signup TableFightThePowerBI_1-1626902916487.png

 

Results TableFightThePowerBI_2-1626902923657.png

 

Sorry for some reason it wont let me upload the file

Hi  @FightThePowerBI ,

 

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

vkellymsft_0-1626920362027.png

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!

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. 

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:

vkellymsft_0-1627291239533.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors