RANKX incorporating legend but retaining overall rank

[Anonymous]

Hi everyone,

I'm having trouble displaying a ranking and incorporating a legend that doesn't do a seperate ranking for each item in the legend.

I have successfully ranked individuals (Appointment_Key) based on an income value (Apportioned award amount) using the DAX:

M: Rank =

RANKX(ALLSELECTED(VW_Denorm_DIM_Appointment[HR_Appointment_Key]), CALCULATE(SUMX(VALUES(VW_Denorm_DIM_Appointment[HR_Appointment_Key]),FACT_RGC_Project_Status_History[M: Apportioned award value])),,,Skip)

When I then incorporate gender into the legend, with the aim of retaining the overall ranking, but colouring individuals by gender, the ranking is then done within each gender and both displayed on the same chart, with more a person of each gender taking up a 1st place, 2nd place etc.

2 rankings when adding a legend.

Gender is stored in another table, DIM_Person, adjacent to the Appointment table.

I've looked over various solutions here, including ALL(Gender), and tried a summarize function as the RANX table expression, but nothing is working. I either end up with everyone ranked 1, or I get back to the double-ranked chart.

Any help greatly appreciated!

Of note:

I am using an on-site server-optimised version of Power BI (may not be the correct terminology), so not all DAX or Power BI functions are available to me (I saw another solution very similar to mine utilising ISINSCOPE, which I don't have available to me on the server-optimised version, and I can't use Power BI's grouping function. I can add columns into the original data tables, but not alculated columns via Power BI).

[v-gizhi-msft]

Hi,

According to your description, i create two simple tables to test:

Then create a rank measure:

Rank = RANKX(ALLSELECTED(Scores),CALCULATE(SUM(Scores[Scores])),,ASC,Skip)

Choose a table visual and it works well:

Then choose Scatter Chart visual, use [Gender] from another table, and it shows the total rank:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Hi Giotto,

Your logic makes sense, and I expected that behaviour when I created the charts, but nonetheless I still can't get my report to do what I need, even following the steps exactly. It is still seemingly doing the calculation for each gender and ranking them seperately. When I tabulate the results you can see this. I don't know if it's my model, or the DAX?

A difference I've just realised though is that unlike your scoring table, my 'Score' is a measure using a column from another dimension, rather than a column native to the table the person IDs are coming from. Is this possibly part of the issue?

[v-gizhi-msft]

Hi,

I create two tables to test:

Then create a total score measure for each person:

Scores = SUM(Scores[ItemScores])

Create a rank measure:

rank = RANKX(ALLSELECTED(Person),[Scores],,DESC,Skip)

The result shows:

The rank still work well.

So could you tell me details about what the relationship among your tables?

Best Regards,

Giotto Zhi

[Anonymous]

Here is a diagram of the model:

I've used your term score, which in my original post is 'Apportioned award value', and is a measure calculated using the 'Score' * an individuals % from the table I've labelled 'Intermediate table'.

Thank you for your continued help 🙂

[v-gizhi-msft]

Hi,

Could you please tell me more details about your tables' relationship such as one-to-one or one-to-many?

And please show the linked column between each two tables.

These info will help me solve your issue as fast as possible.

Best Regards,

Giotto Zhi

[Anonymous]

I've included connecting fields in the above. Join types are illustrated in the connections - forks are many.

Thanks.

[Anonymous · Accepted solution]

Colleagues and I have just cracked it. In the RANX ALLSELECTED statement I was referencing a single column (person ID), but needed to just reference the whole table. Simple oversight on my part (although not immediately obvious!)

Thank you for your help. Reviewing your suggestions helped us get to the answer!

[v-gizhi-msft]

Hi,

If my answer help solve your problem, please mark it as a solution.

Thanks.

Best Regards,

Giotto Zhi