Forum Discussion
Dynamic Rank per Product in Time Period
Hi
I need to be help create a Rank column. I need to be able to generate the rank in the context of the Time Period for the Products based on the Amount and This rank will be in the context of the Market where the market acts as a slicer. I added the sample data and the rank. The DAX I created always gives me a rank 1.
| Markets | Product | Time Period | Amt | Rank |
| North | A | Last 52 Weeks | 1 | 1 |
| North | B | Last 52 Weeks | 2 | 2 |
| North | C | Last 52 Weeks | 3 | 3 |
| North | D | Last 52 Weeks | 4 | 4 |
| North | E | Last 52 Weeks | 5 | 5 |
| North | F | Last 52 Weeks | 6 | 6 |
| North | A | Last 13 Week | 11 | 5 |
| North | B | Last 13 Week | 12 | 4 |
| North | C | Last 13 Week | 4 | 6 |
| North | D | Last 13 Week | 324 | 2 |
| North | E | Last 13 Week | 32 | 3 |
| North | F | Last 13 Week | 3434 | 1 |
| North | A | Last 26 Weeks | 10 | 6 |
| North | B | Last 26 Weeks | 34 | 4 |
| North | C | Last 26 Weeks | 13 | 5 |
| North | D | Last 26 Weeks | 432432 | 1 |
| North | E | Last 26 Weeks | 3423 | 2 |
| North | F | Last 26 Weeks | 124 | 3 |
| North | A | Last 4 Weeks | 1 | 5 |
| North | B | Last 4 Weeks | 4 | 2 |
| North | C | Last 4 Weeks | 7 | 1 |
| North | D | Last 4 Weeks | 2 | 4 |
| North | E | Last 4 Weeks | 3 | 3 |
| North | F | Last 4 Weeks | 0 | 6 |
Any Help is appreciated.
11 Replies
- Greg_Deckler
Community Champion
So, are you OK if this is a measure? I have had far more luck with RANKX as a measure versus as a column. You could create two measures:
MySum = SUM(Table[Amt]) MyRank = RANKX(ALL(Table),[MySum])
You can create a column like this:
Column = RANKX(aRanks,[Amt])
But it won't be grouped like you want. Here is one of the better articles explaining RANKX:
- Zubair_Muhammad
GIve this MEASURE a shot as well
RANK = RANKX ( FILTER ( ALLSELECTED ( Table1 ), Table1[Time Period] = SELECTEDVALUE ( Table1[Time Period] ) ), CALCULATE ( SUM ( Table1[Amt] ) ), , DESC, DENSE )- Zubair_Muhammad
Infact this one should give the proper results with slicers
RANK = RANKX ( CALCULATETABLE ( VALUES ( Table1[Product] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Time Period] = SELECTEDVALUE ( Table1[Time Period] ) ) ), CALCULATE ( SUM ( Table1[Amt] ) ), , DESC, DENSE )
