Formula for Rank (based on a measure)

[gmasta1129]

Hello,

I created a table in Power BI that contains the following columns + one measure

1. Run Date --> Column

2. Facility Code --> Column

3. Facility Name --> Column

4. Overall Score --> Measure

**This table is filtered (Slicer) by "Run Date".

I sorted the table in order by Overall Score (highest value to lowest value) but would like to add a column next to it that shows the rank of each score (ex: 1, 2, 3, 4). 

I entered the formula below to rank the overall score but when I pull the measure into the table, I get a value of 2 for each row and there are 79 rows. 

I was expecting to see 1 through 79 where the highest overall score contains a number of 1 and the lowest contains a number of 79.    

Measure  = RANK(DENSE,ALL(table[portfoliocode]),ORDERBY([Overall Score],ASC),,)

I am not sure what I am doing wrong.  

[v-tejrama]

Hi  @gmasta1129,
Thanks for reaching out to the Microsoft fabric community forum.

I fixed the ranking issue where all facilities showed rank 1 when filtered by changing the ranking logic. Originally, the rank was calculated only within the filtered data context, causing it to reset and show 1 for every filtered row.

To solve this, I used a DAX formula that ranks each facility’s overall score against all facilities in the dataset, ignoring any filters. This way, the rank stays accurate even if you filter for a single facility or date. We rank in descending order so the highest score is rank 1, and we use dense ranking to avoid gaps in ranking numbers when scores tie.

Here’s the DAX measure I used:

DAXCopyEditRank by Overall Score = 
RANKX(
    ALL('FacilityScores'),
    [Overall Score],
    ,
    DESC,
    DENSE
)

Find attached .PBIX for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,

Tejaswi. 

[SundarRaj · Accepted solution]

Hi @gmasta1129 ,
Please try if this gives you the desired result that you are looking for. Thanks

RankX =
RANKX(
ALL('Table'[Facility Code] , 'Table'[Facility Name] , 'Table'[Run Date])
[Overall Score],
,
DESC,
DENSE
)

[gmasta1129]

The formula worked.  

Thank you for your help! 🙂 

[gmasta1129]

Hello @SamsonTruong ,

Thanks for the quick response. I entered the formula and pulled the measure into my table.  I see a value of 1 for each row.  

[SamsonTruong]

Hi @gmasta1129 , by any chance can you attach the DAX for the Overall Score measure?

Additionally, can you try the following DAX measure:

Rank by Score = 
RANKX(
    ALLSELECTED('Table'[Facility Code]),
    CALCULATE([Overall Score]),
    ,
    DESC,
    DENSE
)

[SamsonTruong]

Hi @gmasta1129 ,

Please let me know if the measure below achieves the result you are looking for:

Rank by Score = 
RANKX(
    ALLSELECTED('Table'[Facility Code]),
    [Overall Score],
    ,
    DESC,
    DENSE
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson