Rankx with a column

[Anonymous]

Hi,

I do have a scenerio where i have a fact table with different keys which belongs to different dimensions along with stages and code des as two different columns .

I have a KPI where i want to apply the rank column as a filter so that it will show the top 1 or top 2 according to the filter applied usimg the calulated rank column which is yet to be created .

I was able to achieve it using a measure but i need it as a calculated column 

RANKX(ALLSELECTED(_Data[RelatedWithdrawlCode]),CALCULATE(SUM(Data[Withdrawal_Cnt])),,DESC)

I tried this but didn't work

RANKX (
VALUES (Data[RelatedWithdrawlCode] ),
CALCULATE (
sum ( Data[Withdrawal_Cnt] ),
ALLEXCEPT (Data,Data[RelatedWithdrawlCode] )
),
,
desc,
Dense
)

I wanted to have two ranks column one for the Stages and one for the codes 

There are additional slicers as well on top of the page like month_Year,Prod_code which will also be applied so that it will return accordingly

Please help me create this column as i am struggling and stuck in this scenerio.

Regards
Prabin Nepak 

[Anonymous]

Hi Amit,

I tried using the same formula but the rank  and rank3 are not appearing in the colum.

Instead i am getting very large values.

C1 = sumx(filter(Data,Data[RelatedWithdrawlCode]=earlier(Data[RelatedWithdrawlCode])),Data[Withdrawal_Cnt])
C2=rankx(Data,[C1])

Regards
Prabin Nepak 

[Anonymous]

Hi Amit,

I wanted to have a rank column rather than a measure as i wanted to filter it on a KPI 

Here is sample screenshot what i want is also mentioned

Could you please let me know how to calulate it as columns instead of measures

Regards
Prabin Nepak 

[amitchandak]

@Anonymous , Create these two columns and have rank column on them

W1= sumx(filter(table, [withdrwal stage]=earlier([withdrwal stage])),[cnt])

C1= sumx(filter(table, [codes]=earlier([codes])),[cnt])

like

rankx(all(Table),[W1]) // add other parameters as per need

[amitchandak]

@Anonymous , I am assuming what you are trying a Rank measure, not column. For Rank Refer these links, one the best
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
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

In case you did not find a soltion there, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.