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

     

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)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Harsh. Thanks it works..

     

    If you could also help me with Month on Month Rank change DAx.. My rank is changing month on month basis for same group.

     

    I want to check the Rank difference of this month to last month  (Trend)

     

    Last Month Ranking

    MonthUnitDivison Ranking checkAverage of Star Rating
    FebGroup1213
    FebGroup1122.76
    FebGroup1032.62
    FebGroup942.57
    FebGroup852.36
    FebGroup762.33
    Feb 72.29
    FebGroup582.22
    FebGroup492.11
    Feb 102.06
    FebGroup1112
    FebGroup2111.95
    FebGroup3121.8
    FebTBA13 

     

    This month ranking

    MonthUnitDivison Ranking checkTrend
    MarGroup121312
    MarGroup1175
    MarGroup1041
    MarGroup93-1
    MarGroup83-2
    MarGroup73-3
    Mar 4-3
    MarGroup5102
    MarGroup42-7
    Mar 9-1
    MarGroup19-2
    MarGroup28-3
    MarGroup39-3
    MarTBA4-9

     

    Tend is representing this month to last month rank difference.. I am new in powerbi so not able to apply the Time intellegence function.. I believe you can answer this question too..

     

    Regards

    Uphar

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Can you create a separate post for this as too many questions in one post will confuse future users of the community.

       

      In the other post, pls share proper data in text format, your calendar table structure and your data models to help you on this.

       

      Appreciate your support on this.

       

      Thanks

      HN