Ranking based on score

[baronraghu]

Hi,

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

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

[Anonymous]

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.

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

[amitchandak]

@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

[baronraghu]

Hi All, 

Please see the table below

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

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 

[Anonymous · 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.

2. Create a measure as below to get the ranks

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

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

[baronraghu]

Hi @amitchandak 

When I used first formula as suggested, got this result