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
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.
Solved! Go to Solution.
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] ) ) )
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |