Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
1 year ago
Solved

Formula for Rank (based on a measure)

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 --> Meas...
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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
    )

v-tejrama's avatar
v-tejrama
Community Support
1 year ago

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.