Rankx by Group with filter

[PolarBear]

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

[v-frfei-msft · Accepted solution]

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.

[PolarBear]

@v-frfei-msft ,

This worked perfect! Thank you!!