Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
baronraghu
Helper III
Helper III

Ranking based on score

Hi,

In the below table, I am trying to rank the column  in blue based on value in red.

baronraghu_0-1638793177151.png

I used RANKX(AllEXCEPT(TABLE,COLUMN4,COLUMN5),COLUMN_RED) all i am getting is 5437 Dont Know why

1 ACCEPTED SOLUTION

Hi @baronraghu ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Add an index column to uniquely identify each row. Since there are only numeric columns in the table, this step can be dispensed with if there are other columns in the table that can uniquely identify each row.

yingyinr_0-1640227281621.png

2. Create a measure as below to get the ranks

Ranks = 
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Total score] ) ),
    ,
    DESC,
    DENSE
)

yingyinr_1-1640227509037.png

If the above one is not your expected result, please share more details(more raw data with Text format, the formula of involved measure/calculated column and calculation logic etc.) with us in order to provide you a suitable solution.  Those in below table are correct ones? Thank you.


P- BId CS Bid St BId P Score CS Score St Score Total Score Ranks
    19,52,738                1,260                                858.86                0.494                0.195                0.082                   0.77 1
    19,19,986                1,259                                891.83                0.486                0.195                0.085                   0.77 2
    19,30,467                1,240                                890.07                0.488                0.192                0.085                   0.77 3
    19,57,237                1,122                                958.58                0.495                0.174                0.092                   0.76 4
    18,34,982                1,291                                997.39                0.464                0.200                0.096                   0.76 5
    18,46,377                1,257                                972.28                0.467                0.195                0.093                   0.76 6
    19,12,371                1,211                                867.30                0.484                0.188                0.083                   0.75 7
    19,26,149                1,173                                877.35                0.487                0.182                0.084                   0.75 8
    18,07,818                1,258                            1,011.28                0.457                0.195                0.097                   0.75 9
    18,51,176                1,283                                834.65                0.468                0.199                0.080                   0.75 10

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

Hi @baronraghu ,

Could you please provide raw data for these fields referenced in your table visual?It seems like you are creating a calculated column, do you add the aggregation function (like Sum) when you apply it to the table visual? Are the data type of COLUMN4 and COLUMN5 in the formula Text type? Is COLUMN_RED an fact field in the table or a measure? If it is a measure, could you please provide the relevant formula?Thank you.

yingyinr_0-1639120252802.png

In addition, there are several blogs on how to rank, you can refer them to achieve your needs.

Showing Ranking in a Visual in Power BI using RANKX DAX function

Color and size ranked by Sales = 
RANKX(
    ALL(DimProduct[Color],DimProduct[Size]),
    [Sales]
)

Ranking Values with Measures

Ranking Values with Power Query

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@baronraghu , Need of allexcept ? you want sub category rank ?

 

RANKX(TABLE,[COLUMN_RED])

 

or

 

RANKX(Filter(TABLE,[COLUMN4] = earlier( [COLUMN4]) && [COLUMN4] = earlier( [COLUMN4])  ),COLUMN_RED)

 

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Hi All, 

 

Please see the table below

 

P- BIdCS BidSt BIdP ScoreCS ScoreSt ScoreTotal ScoreRanks
    19,52,738               1,260                               858.86               0.494               0.195               0.082                  0.771
    19,19,986               1,259                               891.83               0.486               0.195               0.085                  0.772
    19,30,467               1,240                               890.07               0.488               0.192               0.085                  0.773
    19,57,237               1,122                               958.58               0.495               0.174               0.092                  0.764
    18,34,982               1,291                               997.39               0.464               0.200               0.096                  0.765
    18,46,377               1,257                               972.28               0.467               0.195               0.093                  0.766
    19,12,371               1,211                               867.30               0.484               0.188               0.083                  0.757
    19,26,149               1,173                               877.35               0.487               0.182               0.084                  0.758
    18,07,818               1,258                           1,011.28               0.457               0.195               0.097                  0.759
    18,51,176               1,283                               834.65               0.468               0.199               0.080                  0.7510

 

P Score is the indiviual P bid value *0.5/ Max of P bid

CS Score is the indiviual CS bid value *0.2/ Max of CS Bid

St Score is the indiviual St bid value *0.5/ Max of St bid

Total score= P score+ CS score+ St Score

 

Now i am having trouble in ranking the Total score 

Hi @baronraghu ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Add an index column to uniquely identify each row. Since there are only numeric columns in the table, this step can be dispensed with if there are other columns in the table that can uniquely identify each row.

yingyinr_0-1640227281621.png

2. Create a measure as below to get the ranks

Ranks = 
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Total score] ) ),
    ,
    DESC,
    DENSE
)

yingyinr_1-1640227509037.png

If the above one is not your expected result, please share more details(more raw data with Text format, the formula of involved measure/calculated column and calculation logic etc.) with us in order to provide you a suitable solution.  Those in below table are correct ones? Thank you.


P- BId CS Bid St BId P Score CS Score St Score Total Score Ranks
    19,52,738                1,260                                858.86                0.494                0.195                0.082                   0.77 1
    19,19,986                1,259                                891.83                0.486                0.195                0.085                   0.77 2
    19,30,467                1,240                                890.07                0.488                0.192                0.085                   0.77 3
    19,57,237                1,122                                958.58                0.495                0.174                0.092                   0.76 4
    18,34,982                1,291                                997.39                0.464                0.200                0.096                   0.76 5
    18,46,377                1,257                                972.28                0.467                0.195                0.093                   0.76 6
    19,12,371                1,211                                867.30                0.484                0.188                0.083                   0.75 7
    19,26,149                1,173                                877.35                0.487                0.182                0.084                   0.75 8
    18,07,818                1,258                            1,011.28                0.457                0.195                0.097                   0.75 9
    18,51,176                1,283                                834.65                0.468                0.199                0.080                   0.75 10

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @amitchandak 

 

When I used first formula as suggested, got this result 

baronraghu_0-1638794637974.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors