Forum Discussion
Rankx function for period group
Hi all, I' working with rankx function. My ask is to get, lets say, top 2 ranks across the selected time period.
Lets say below is my data -
| Brand | Sales | Period | Quarter Period |
| A | 30 | Jan'21 | 1Q21 |
| A | 12 | Feb'21 | 1Q21 |
| A | 14 | Mar'21 | 1Q21 |
| B | 16 | Jan'21 | 1Q21 |
| B | 18 | Feb'21 | 1Q21 |
| B | 20 | Mar'21 | 1Q21 |
| C | 22 | Jan'21 | 1Q21 |
| C | 24 | Feb'21 | 1Q21 |
| C | 26 | Mar'21 | 1Q21 |
| D | 28 | Jan'21 | 1Q21 |
| D | 30 | Feb'21 | 1Q21 |
| D | 32 | Mar'21 | 1Q21 |
| A | 10 | Apr'21 | 2Q21 |
| ... | ... | ... | ... |
and I would like to display something like this -
| Brand | Period | Quarter Period | Rank |
| A | Feb'21 | 1Q21 | |
| A | Jan'21 | 1Q21 | |
| A | Mar'21 | 1Q21 | |
| B | Feb'21 | 1Q21 | 3 |
| B | Jan'21 | 1Q21 | 3 |
| B | Mar'21 | 1Q21 | 3 |
| C | Feb'21 | 1Q21 | 2 |
| C | Jan'21 | 1Q21 | 2 |
| C | Mar'21 | 1Q21 | 2 |
| D | Feb'21 | 1Q21 | 1 |
| D | Jan'21 | 1Q21 | 1 |
| D | Mar'21 | 1Q21 | 1 |
In short, I would like all of my monthly period to have same rank as the quarter.
What I tried: calculate(if(rankx(all('Table'[brands]), 'Table'[sales])<=2, rankx(all('Table'[brands]), 'Table'[sales]))
Hope I was able to simplify my actual problem to a point.
Hi Arpit_Jain
Avantika-Thakur 's solution is helpful. In addition, if you want to get the top 2 ranks in every quarter period, you can add Quarter Period to ALLEXCEPT function too.
Rank = VAR _rank = RANKX(ALL('Table1'[Brand]),CALCULATE(SUM('Table1'[Sales]),ALLEXCEPT('Table1','Table1'[Brand],Table1[Quarter Period]))) RETURN IF(_rank<=2, _rank, BLANK())Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi Arpit_Jain ,
You can try using the below measure -
RANKX(ALL('Table '[Brand]),calculate(SUM('Table'[Sales]),ALLEXCEPT('Table','Table '[Brand])))Hope this helps!Thanks!
AvantikaHi Arpit_Jain
Avantika-Thakur 's solution is helpful. In addition, if you want to get the top 2 ranks in every quarter period, you can add Quarter Period to ALLEXCEPT function too.
Rank = VAR _rank = RANKX(ALL('Table1'[Brand]),CALCULATE(SUM('Table1'[Sales]),ALLEXCEPT('Table1','Table1'[Brand],Table1[Quarter Period]))) RETURN IF(_rank<=2, _rank, BLANK())Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
