Rankx with ASC starting rank at 2

[Anonymous]

Hello Experts ,

Rankx fx with ASC is starting at 2  any reason ?  

With DESC woking fine ..

Here is my logic 

Rank Overtime = Rankx(allselected('VCS VCS Mapping'[FACILITY]),[mm], ,ASC,Dense)

Ave OverTime = SUM('VCS OverTime'[Total Hours]) / SUM('VCS OverTime'[OT Hours])

Rank is applied on Ave Over time . TIA

[OwenAuger · Accepted solution]

Hi @Anonymous 

Try this instead:

Rank Overtime =
RANKX (
    CALCULATETABLE ( VALUES ( 'VCS VCS Mapping'[FACILITY] ), ALLSELECTED () ),
    [Ave OverTime],
    ,
    ASC,
    DENSE
)

In your original measure, ALLSELECTED( .... ) returns all values of FACILITY in the overall filter context of the visual, not taking into account any cross filtering from filters such as REGION.

For any facilities that don't exist in the context of the other filters applied (e.g. Facilities not in the Central region), [Ave OverTime] is therefore blank (equivalent to zero for the purpose of RANKX), and they are tied with rank 1.

The change I made above changes the first argument of RANKX to include only Facilities that exist in the overall filter context of the visual.

Does this work for you?

Regards,

Owen

[ValtteriN · Accepted solution]

Hi,

This behaviour is likely caused by your ALLSELECTED referencing a column. When this column is blank and filtered RANKXX faces issues. Here I recreated the issue:

In this example I refrer to the table instead of the column:

So as a solution you could refer to the table. I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[OwenAuger · Accepted solution]

Hi @Anonymous 

Try this instead:

Rank Overtime =
RANKX (
    CALCULATETABLE ( VALUES ( 'VCS VCS Mapping'[FACILITY] ), ALLSELECTED () ),
    [Ave OverTime],
    ,
    ASC,
    DENSE
)

In your original measure, ALLSELECTED( .... ) returns all values of FACILITY in the overall filter context of the visual, not taking into account any cross filtering from filters such as REGION.

For any facilities that don't exist in the context of the other filters applied (e.g. Facilities not in the Central region), [Ave OverTime] is therefore blank (equivalent to zero for the purpose of RANKX), and they are tied with rank 1.

The change I made above changes the first argument of RANKX to include only Facilities that exist in the overall filter context of the visual.

Does this work for you?

Regards,

Owen

[Anonymous]

THANK YOU for your effort.

I have a Question 

What if My Visual Was a card ? I think ALLSELECTED is not the right choice for this situation.

[Anonymous]

Hey @OwenAuger ,

How can I write same measure in Calculated column ?

I see circular dependancy  while using CC...

Thanks for your help

[OwenAuger]

Hi there @Anonymous 

That formula is designed as a measure, so could well have unexpected results in a calculated column.

If you want a Rank in a calculated column, we would need to rewrite slightly, depending which table it's in.

Could you post a model diagram, or a santised PBIX?

Are you adding it to 'VCS VCS Mapping', and you want each FACILITY to have the same rank?

Regards,

Owen

[Anonymous]

Here is sample data @OwenAuger 

Rank by facility based on Ave values (Vacancy, SMS, Tenure).

Your solution was working fine but i need it in CC.

Thanks a LOT.

REGION	FACILITY	Vacancy	Tenure	Date	sms
USA	Stewart	2	4000	5/1/2021	10
USA	Stewart	50	4000	7/1/2020	10
USA	Stewart	50	4000	9/1/2020	10
USA	Whitehorse	50	4000	10/1/2020	10
USA	Whitehorse	50	4000	11/1/2020	10
USA	Whitehorse	50	4000	12/1/2020	10
USA	Whitehorse	30	20	2/1/2021	32.22
USA	Whitehorse	30	20	5/1/2021	32.22
USA	Ap	30	20	7/1/2020	32.22
CANADA	Market	30	20	9/1/2020	32.22
CANADA	Market	30	20	10/1/2020	32.22
CANADA	Market	30	20	11/1/2020	32.22
CANADA	Moon	2	20	12/1/2020	32.22
CANADA	Moon	2	20	3/1/2021	32.22
CANADA	Moon	2	340	5/1/2021	3.43
CANADA	Moon	2	340	6/1/2021	3.43
CANADA	West Lane	5	340	7/1/2020	3.43
CANADA	West Lane	5	340	8/1/2020	3.43
CANADA	West Lane	5	340	9/1/2020	3.43
CANADA	West Lane	5	340	10/1/2020	3.43
SA	Woods	5	340	12/1/2020	3.43
SA	Woods	5	50	2/1/2021	3.43
SA	Woods	5	50	3/1/2021	3.43
SA	SHOP	789	50	4/1/2021	3.43
SA	SHOP	789	50	6/1/2021	3.43
SA	SHOP	789	50	9/1/2020	3.43
SA	SHOP	789	50	10/1/2020	3.43
SA	Bedford	789	50	11/1/2020	3.43
SA	Bedford	789	50	12/1/2020	3.43
SA	Bedford	789	50	2/1/2021	3.43