Calculate the rank based on number of times a value occurs in a column (excluding specific values)

[misterlau14]

Hi everyone, I'd like to receive some assistance please

Background

I have a column of Occupation data that contains various reported responses.

New column created

So far, I have created a new column that counts the number of times each reported response occurs in the 'Occupation' column.

Count_Occupation =

COUNTROWS(

    FILTER('ALL Arrivals data', 'ALL Arrivals data'[Occupation] = EARLIER('ALL Arrivals data'[Occupation])))

 

This new column produces the correct output as required, so no issues here.

Ideal output

I would like to create a new column (or a new measure) that calculates the rank (based on the count of the number of times that each reported response occurs in the 'Occupation' column) - however it has to exclude the following reported responses:

• Not Stated
• Not Previously Employed

What I have created so far

I have currently created a new column called Rank_Count_Occupation that calculates the rank, however it does not exclude the 2 responses mentioned above.

Rank_Count_Occupation =

RANKX('ALL Arrivals data',('ALL Arrivals data'[Count_Occupation]),,DESC, Dense)

 

This is the current output in a table:

Ranking	Occupation	Number of arrivals
1	Not Stated	4891
2	Non Working Child	4522
3	Not Previously Employed	4114
4	Occupation Unknown	3526
5	Home Duties	1846

However, the output I am wanting is in this table below:

Ranking	Occupation	Number of arrivals
	Not Stated	4891
1	Non Working Child	4522
	Not Previously Employed	4114
2	Occupation Unknown	3526
3	Home Duties	1846

Seeking your assistance
I think I'm 80% of the way there with the new columns I have created up to this point but need to add in one last thing to the current DAX code to exclude the 'Not Stated' and 'Not Previously Employed' responses.

 

Can someone please help me with how to go about creating a new column (or a new measure) to obtain the required output (in purple text) in the second table above?

Thanks all

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[misterlau14]

Power BI file - Occupation data only 

Hi, please see above link.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure 1 = RANK(DENSE,FILTER(ALL('ALL Arrivals data'[Grouping_Occupation]),'ALL Arrivals data'[Grouping_Occupation]<>"Not stated"&&'ALL Arrivals data'[Grouping_Occupation]<>"Not previously employed"),orderby([Count_Occupation_],DESC))

Hope this helps.

[misterlau14]

Thank you so much for your help - your measure did the job nicely 🙂

[Ashish_Mathur]

You are welcome.