RANK with multiple categories which is dynamic with filters

[Anonymous]

Hi

I cant seem to get the rank calculation to work for my data - im pretty new to PowerBI and an avid googler 🙂

I am trying to RANK parentbank per CalendarWeek, by Available Cash in DESC order. The RANK must be dynamic (work with filters - calendar week, country).

Caledar Week filter will always be a single-select, country filter could by mutiple.

Here is a sample of the data, there are no relationships, its a flat file. 

Expected Result : for each date, show me the DESC order of 'Parent Banks' ranked by 'Available Cash' Value

ie. 

CalendarWeek	ParentBank	Country	Available Cash	Rank
12/12/20	A	A	3000	2
12/12/20	B	A	5000	1
12/12/20	C	B	2000	3
24/5/19	A	A	9000	2
24/5/19	B	A	200	3
24/5/19	C	B	36666666	1

[DataInsights]

@MelissaBee1986,

See the link below for a useful explanation of various RANKX uses. In addition, you appear to have created a calculated column. Because you want the range to be dynamic (work with filters), you must create a measure.

https://powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/

[Anonymous]

Thank you for getting back to me 😊 I managed to calculate RANK with a measure!

The reason why I created a column before is because I wanted to create a category after which details  ParentBank + Others if over RANK 20.(I will look into other ways around this).