Forum Discussion
Rankx with a column
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
4 Replies
- amitchandak
Super User
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/367415In 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.
- 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
