Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

RankX excluding some values

Hi,

 

I want to exclude some value or want filter some values while calculating the Rank. I am calculating the Ranks on the basis of average ranking given on some parameters.

 

I am using below DAX but not getting the right answer

 

Divison Ranking check =
RANKX(ALL(Table),CALCULATE(AVERAGE(Score[Star Rating]),FILTER(Table,Table[Unit]<>"Group1")),,DESC,Dense)
 
I want to exlude Group1 while calculating the Rankx.. Need urgent help.
 
See: I am getting 1 in all ranking also Group1 is there in the list.
 
Raw table is given below
 
UnitRank CheckAverage of Star Rating
Group1213
Group1112.76
Group1012.62
Group912.57
Group812.36
Group712.33
 12.29
Group512.22
Group412.11
 12.06
Group112
Group211.95
Group311.8
 
amitchandak Could you please help
 Regards
Uphar
RankX excluding Some Values
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Then try this measure.

     

    Rank Without GRp 1 = 

IF(
    SELECTEDVALUE('Table'[Unit]) = "Group1", BLANK(),
RANKX (FILTER(ALL('Table'),'Table'[Unit] <> "Group1"), CALCULATE(AVERAGE('Table'[Average of Star Rating]))))

     

    Regards,

    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

     

    Use this measure

     

    Rank without Grp 1 = RANKX (FILTER(ALL('Table'),'Table'[Unit] <> "Group1"), CALCULATE(AVERAGE('Table'[Average of Star Rating])))

     

    Post this apply a visual filter

     

     

     

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)


    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Harsh, Thanks for your  quick response. 

       

      But I dont want to filter the Unit using visual filter I am looking for no rank if I am filtering out any group.

       

      I have applied the same DAX and got below results

       

      UnitDivison Ranking checkAverage of Star Rating
      Group1213
      Group1122.76
      Group1032.62
      Group942.57
      Group852.36
      Group762.33
       72.29
      Group582.22
      Group492.11
       102.06
      Group1112
      Group2111.95
      Group3121.8
      TBA13 

       

      Here, I am filtering out the group2 but see group2 have 11th Rank.. Instead of this Is there any way we can make it blank or remove that group?

       

      I wan to rank them indivually like : Group1 having 11 rating out of 13 group or 11/13(its a total group count)

       

      Regards

      Uphar

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Then try this measure.

         

        Rank Without GRp 1 = 

IF(
    SELECTEDVALUE('Table'[Unit]) = "Group1", BLANK(),
RANKX (FILTER(ALL('Table'),'Table'[Unit] <> "Group1"), CALCULATE(AVERAGE('Table'[Average of Star Rating]))))

         

        Regards,

        Harsh Nathani

        Appreciate with a Kudos!! (Click the Thumbs Up Button)