RANKX

[m_e_1203]

Hello,

I am trying to creating ranking, somehow, DAX below provides this ranking below.

Original Table (WorkHour) contains [ITEM_LINE] daily per [BID_Assembler]. And I would like to calculate YTD ranking by total of [ITEM_LINE].

DateKey	BID_Assembler	ITEM_LINE
4/1/2019	A	394
4/1/2019	B	331
4/1/2019	C	383
4/1/2019	D	325
4/1/2019	E	116
4/1/2019	F	312
4/1/2019	G	243
4/1/2019	H	5

Total Pick Line = SUM( WorkHour[ITEM_LINE])

Rank Total Pick Line = RANKX(ALLSELECTED(WorkHour), CALCULATE([Total Pick Line], ALLEXCEPT(WorkHour, WorkHour[BID_Assembler])) , , DESC)

BID_Assembler	ITEM_LINE	Total Pick Line	Rank Total Pick Line
A	190,560	190,560	1
B	188,828	188,828	471
C	155,889	155,889	940
D	154,284	154,284	1,400
E	148,067	148,067	1,847
F	146,787	146,787	2,092
G	140,904	140,904	2,490
H	137,873	137,873	2,842
I	137,746	137,746	3,296
J	134,799	134,799	3,763
K	134,426	134,426	4,203

Why this does not show correct ranking?

Thank you.

[amitchandak · Accepted solution]

@m_e_1203 , You have to create a rank like

Rank Total Pick Line = RANKX(ALLSELECTED(WorkHour[BID_Assembler]), [Total Pick Line] , , DESC)

if you need ytd create a ytd measure have rank on that

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

[m_e_1203]

Thank you so much!! Worked!!