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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 4
Anonymous
Not applicable

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.

Capture14.PNG

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



Regards
Prabin Nepak 

Anonymous
Not applicable

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

 
 

Capture10.PNG

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

Regards
Prabin Nepak 

@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
Super User
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/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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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