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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
abayless
Advocate II
Advocate II

Return rank by group (identifying group selected member belongs to)

I have two tables:

Sales data -- contains [Revenue], [ID]
Store List -- contains [Market], [Store], [ID]
These tables are linked by [ID]. I have a page-level filter on 'Store List'[Store], as its a report that allows you to see various details on an individual store. What I need is a way to return the individual stores [Revenue] rank within its own market. So if you select store 123 and its in Market 1, then I'd like to return the individuals stores [Revenue] rank among only stores in Market 1. Rank should look something like this:

 

 

Market     Store      Revenue      Rank

Mar 1        123           100            2

Mar 1        345           110            1

Mar 1        267            95             3

Mar 2        357           548            1

Mar 2        795           405            2

 

Since I have a filter for store, I'd like to create a card that just returns this Rank when I select a store. Most of my issue seems to be not understanding how to make that selection hierachy work and correctly group. If I select a single store, I need to identify the market it belongs to, rank all the stores in that market, then return that single stores rank.

 

Here are some things I tried:

 

Rank = RANKX ( ALLSELECTED('Store List'),CALCULATE(SUM('Sales Data'[Revenue])))
This gives every selected store a rank, but not automatically according to the [Market] group. If I filter down to a market it will rank just the selected stores, but it defaults to 1 whenever a single store is selected.

 

Rank = RANKX ( ALLSELECTED('Store List'[Market]),CALCULATE(SUM('Sales Data'[Revenue])))
This was suggested online to account for the grouping, but it doesn't seem to work at all. All stores are given a rank of 1.

 

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION
Chihiro
Solution Sage
Solution Sage

Try something like...

Rank (Market) =
RANKX (
    FILTER (
        ALL ( 'Store List'[Store], 'Store List'[Market] ),
        'Store List'[Market] = MAX ( 'Store List'[Market] )
    ),
    CALCULATE ( SUM ( 'Sales Data'[Revenue] ) )
)

View solution in original post

3 REPLIES 3
Chihiro
Solution Sage
Solution Sage

Try something like...

Rank (Market) =
RANKX (
    FILTER (
        ALL ( 'Store List'[Store], 'Store List'[Market] ),
        'Store List'[Market] = MAX ( 'Store List'[Market] )
    ),
    CALCULATE ( SUM ( 'Sales Data'[Revenue] ) )
)

Thank you -- this worked! Additional question: what is the purpose of the MAX function here? 

MAX is used to filter table columns by row context of [Store] within Filter function to reduce table to smaller set.

 

You can find more detail explanation of the concept used in link below.

http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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