cancel
Showing results for
Did you mean:
Helper I

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Rankx by Group with filter

Hi @PolarBear ,

``````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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
2 REPLIES 2
Community Support

## Re: Rankx by Group with filter

Hi @PolarBear ,

``````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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Helper I

## Re: Rankx by Group with filter

This worked perfect! Thank you!!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors