Assistance with Ranking and Conditional Formatting

[atin]

Hi everyone,

I’m currently working on ranking visuals for the recently concluded 2025 FIFA World Cup, which ended last Sunday with Chelsea FC (a UK football club) lifting the trophy.

(Details of the Table with the dataset: Table Name is TeamStats.
The are 11 columns in the TeamStats Table and the Column Names in the table are:
Group Type | Team | Country | MP | Won | Draw | Lost | GA | GF | GD |  Points

Here’s what I’m trying to achieve:

• Rank the top two teams in each group (Groups A to H) based on:

1. Points
2. If tied, then by Goal Difference (GD)
3. If still tied, then by Goals For (GF)

Only the 1st and 2nd placed teams in each group should be considered.

Some of you may recognise the dataset from the tournament. I've shared visuals below for your review. However, the conditional formatting I applied doesn't seem to work correctly for some of the groups — the top two teams by points (and subsequent tie-breakers) are not consistently highlighted in green as intended.

Measures Created: ( Details below)

I’ve created three measures to support this ranking logic. These were then used in the conditional formatting section:

Format: By Field Value

Based on: HighlightTop2 and OK

The current results are reflected in the visuals I’ve shared - see below

Could you kindly take a look and advise on what might be going wrong?

Thanks in advance for your support!

VISUALS: 

1st measure: RankKey

RankKey =

MAX(TeamStats[Points] ) * 1000 +

MAX(TeamStats[GD] ) * 100 +

MAX(TeamStats[GF] )

2nd Measure: RANK MEASURE

Rank =

IF(

    HASONEVALUE(TeamStats[Group Type] ),

    RANKX(

        FILTER(

            ALL(TeamStats ),

            TeamStats[Group Type] = SELECTEDVALUE(TeamStats[Group Type] )

        ),

        [RankKey] ,

        ,

        DESC,

        Dense

    ),

    BLANK() )

3rd Measure: HighlightTop2

HighlightTop2 =

IF(

    [Rank] <=2, "#C6EFCE", BLANK() )

[ajaybabuinturi · Accepted solution]

Hi @atin,

I believe your alomost correct, you can try with below modified DAX.

I would suggest to multiply points by higher factor(10,000 instead of 1000) to reduce chances of key collisions.

1st measure(Modified): RankKey

RankKey =

MAX(TeamStats[Points] ) * 10000 +

MAX(TeamStats[GD] ) * 100 +

MAX(TeamStats[GF] )

Make sure the Rank measure evaluates per group per visual row, not with unexpected context.

2nd Measure(modified): RANK MEASURE

Rank =

VAR _group = SELECTEDVALUE(TeamStats[Group Type])

RETURN

    RANKX(

        FILTER(

            ALL(TeamStats ),

            TeamStats[Group Type] = _group

        ),

        [RankKey] ,

        ,

        DESC,

        Dense

    ),

    BLANK() )

To prevent any BLANK() that might cause formatting issues

3rd Measure(modified): HighlightTop2

HighlightTop2 =

IF(

    [Rank] <= 2,
    "#C6EFCE", // Highlight for top 2
    "#FFFFFF" // White (or you can use "transparent")
)

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[v-tsaipranay · Accepted solution]

Hi @atin ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Your method of ranking teams within each group based on Points, Goal Difference (GD), and Goals For (GF) follows standard tournament rules and makes perfect sense.

After reviewing your measures and visuals, it looks like the issue with conditional formatting is mainly due to:

• The way RankKey is calculated it might create the same value for teams with similar stats.
• The group filter not being applied correctly in your RANKX formula.
• Using BLANK() in the formatting, which can cause display issues.

Also thank you @ajaybabuinturi  for the response shared, which accurately addresses these points. In particular:

• Using Points * 10,000 in RankKey makes the ranking more accurate.
• Adding a VAR _group in your Rank measure ensures it ranks teams correctly within each group.
• Returning a color like white instead of BLANK() in HighlightTop2 avoids formatting problems in visuals.

These updates should help you highlight the top two teams correctly in each group.

Hope this helps. Please reach out for further assistance.

Thank you.

[ajaybabuinturi · Accepted solution]

Hi @atin,

I believe your alomost correct, you can try with below modified DAX.

I would suggest to multiply points by higher factor(10,000 instead of 1000) to reduce chances of key collisions.

1st measure(Modified): RankKey

RankKey =

MAX(TeamStats[Points] ) * 10000 +

MAX(TeamStats[GD] ) * 100 +

MAX(TeamStats[GF] )

Make sure the Rank measure evaluates per group per visual row, not with unexpected context.

2nd Measure(modified): RANK MEASURE

Rank =

VAR _group = SELECTEDVALUE(TeamStats[Group Type])

RETURN

    RANKX(

        FILTER(

            ALL(TeamStats ),

            TeamStats[Group Type] = _group

        ),

        [RankKey] ,

        ,

        DESC,

        Dense

    ),

    BLANK() )

To prevent any BLANK() that might cause formatting issues

3rd Measure(modified): HighlightTop2

HighlightTop2 =

IF(

    [Rank] <= 2,
    "#C6EFCE", // Highlight for top 2
    "#FFFFFF" // White (or you can use "transparent")
)

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result there clearly.