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

6 Replies

  • SamsonTruong's avatar
    SamsonTruong
    Super User
    1 year ago

    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

  • SundarRaj's avatar
    SundarRaj
    Super User
    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
    )

    • gmasta1129's avatar
      gmasta1129
      Resolver I
      1 year ago

      The formula worked.  

      Thank you for your help! 🙂 

  • gmasta1129's avatar
    gmasta1129
    Resolver I
    1 year ago

    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's avatar
      SamsonTruong
      Super User
      1 year ago

      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
)
  • 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.