Forum Discussion
Solved
Rankx by Group with filter
Good Morning,
I've tried googling this for a couple of days now but haven't found a solution that works for me..and my deadline is tomorrow 😕
I have a summarize table that I need to rank by category (category) and subcategory (YearWeekNum), only if the count is > 999. I need the Rank to be a column, because I have to use it for another calculation (consecutive weeks in top 10).
| Category | Avg. | Count | YearWeekNum | Rank | Consecutive Weeks in top 10 |
| A | 4.2 | 1000 | 2020-4 | 1 | 2 |
| A | 4.2 | 222 | 2020-4 | 0 | |
| B | 3.8 | 2000 | 2020-4 | 2 | 2 |
| C | 3.2 | 1500 | 2020-4 | 3 | 2 |
| A | 3.6 | 2800 | 2020-3 | 3 | 2 |
| B | 4.1 | 1200 | 2020-3 | 1 | 2 |
| C | 3.9 | 1800 | 2020-3 | 2 | 2 |
| A | 4.2 | 222 | 2020-4 | 1 | 2 |
I've created a rank column using:
Rank =
Var WC = IF(Weekly_Stats[Count] > 999, 1)
Return
IF(WC <> BLANK(),
RANKX(
FILTER(
Weekly_Stats,
Weekly_Stats[YearWeekNum] = EARLIER(Weekly_Stats[YearWeekNum])
),
Weekly_Stats[Weekly Average], , DESC, Dense
)
)
The problem is that while it is leaving those with a count < 1000 blank in the column itself, it is still including them in the Rank #.. for example, Rank 3 below should actually be 1
| Category | Avg. | Count | YearWeekNum | Rank |
| A | 5.8 | 382 | 2020-4 | |
| A | 5 | 691 | 2020-4 | |
| A | 4.2 | 1000 | 2020-4 | 3 |
Thank you in advance for your help
Hi PolarBear ,
Please update your formula as below.
ank = IF ( Weekly_Stats[Count] > 999, RANKX ( FILTER ( Weekly_Stats, Weekly_Stats[YearWeekNum] = EARLIER ( Weekly_Stats[YearWeekNum] ) && Weekly_Stats[Count] > 999 ), Weekly_Stats[Avg.], , DESC, DENSE ), BLANK () )Also attached the pbix.
2 Replies
Hi PolarBear ,
Please update your formula as below.
ank = IF ( Weekly_Stats[Count] > 999, RANKX ( FILTER ( Weekly_Stats, Weekly_Stats[YearWeekNum] = EARLIER ( Weekly_Stats[YearWeekNum] ) && Weekly_Stats[Count] > 999 ), Weekly_Stats[Avg.], , DESC, DENSE ), BLANK () )Also attached the pbix.
