Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.