RANKX Using A Parameter

[alee5210]

Hello everyone, I am trying to use a parameter with the RANKX function. The situation is like the below

I have 3 tables

1. 'Store' - A lookup table which matches one value to another. There is a relationshiop between this table and Table 3 below linking the store names. There are around 100 stores.

Store Anonymous Name		Store Real Name
Store AAA		Store Alabama
Store BBB		Store Birchgrove
Store CCC		Store California

2. 'Store Param' - A parameter which uses Table 1 so I can switch between the 2 values from Table 1. Not all people will have access to the real store names but all people will have access to the anonymous store names which is why I have created this parameter.

The code is:

Store Parameter = {

    ("Store Anonymous Name", NAMEOF('Store'[Store Anonymous Name]), 0),

    ("Store Real Name", NAMEOF('Store'[Store Real Name]), 1)}

The table looks like

Store		Parameter		Parameter Order
Store Anonymous Name		Store[Store Anonymous Name]	0
Store Real Name		Store[Store Real Name]	1

3. 'Figures' - A table which has all my numbers which I use to sum/average. There is a relationship between table 1 and this one linking the store names.

Store Anonymous Name		Date		Transactions	Items Sold
Store AAA		01/01/2024		30		38
Store AAA		02/01/2024		23		42
Store AAA		03/01/2024		18		40
Store AAA		04/01/2024		21		39
Store AAA		05/01/2024		26		44
Store BBB		01/01/2024		17		47
Store BBB		02/01/2024		30		37
Store BBB		03/01/2024		27		33
Store BBB		04/01/2024		7		32
Store BBB		05/01/2024		22		46
Store CCC		01/01/2024		22		38
Store CCC		02/01/2024		26		31
Store CCC		03/01/2024		13		43
Store CCC		04/01/2024		30		43
Store CCC		05/01/2024		25		43

I have a chart bar chart which shows the number of sales per store where I've used the parameter for the store. As mentioned earlier, this is done beacuse some people can only see the anonymous store name and others can see the real store name. At the moment I can sort it by sales or alphabetical by store.

I want to sort it by which store has the highest number of items sold. I have found I can do this by using a measure like the one below and then putting it into the tooltip but this only works when not using the parameter.

Store Rank By Items Sold = rankx(allselected('Figures'[Store Anonymous Name]), calculate(sum('Figures'[Items Sold])))

Is there a way to make this work using the parameter so that no matter if users have access to he anonymous store name or real store name, the chart is ranked correctly?

[amitchandak]

@alee5210 , if you sort on sol measure, it should remain on that.

If you want to switch rank based on field parameter selection, refer

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

You can use Rank as Var or measure to switch

If you want keep sorting on Store/Axis, when you switch, use this

Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw

[alee5210]

I assume you mean if I sort on the 'Store Rank By Items Sold' measure it should stay like that? But it does not, because in my chart I am using the parameter rather than the Figures'[Store Anonymous Name] field.

Also I am not looking to do top N, I want all stores to be in the data. Will this still be an option if the date range has been cut and there are very few stores but my top N says top 50 stores? Will there be a lot of blanks left over?