Rank on % but want to omit items where the underlying data has a value of less than X

[kathrynhmoss]

Hi, 

I have a ranking formula which ranks institutions by a %. This is based on X amount of people (total) / X amount that qualify. 

The rank formula is working well. However, I would like to omit any institutions that  have a total of people that is less than a certain amount eg, if the number of people is less than X in my total, i do not want them to appear in my table. 

This is the table below that i need some help with. 

The institution at the top has a % of 100% and so is ranked 1. However, i want to omit this institution from my ranks because it does not contain enough people. 

Therefore, if this institution gets omited i would like the institution below (89.5%) to then become number 1 in the rank. 

Does anyone have any idea how i go about writing a measure to do this please? 

Any help would be gratefully apprecaited. 🙂 

[smpa01]

@kathrynhmoss  can you please provide sampel data and expected output?

[kathrynhmoss]

Hi there. 

Thanks for getting back to me.

Do you mean for the second part of the problem? 

I am not sure how much more data to put up other than what i have posted above in response to Amitchandak's response as this is bascially all i have in terms of being able to demonstrate the data. Can you let me know what else you need to see so that i can get that together for you please? 🙂

Thanks

Kathryn 

[v-easonf-msft]

Hi, @kathrynhmoss 

You can try formula as below:

Rank2 = 
IF (
    [number of people] >= 1000000,
    //if x=1000000
    RANKX (
        FILTER ( ALLSELECTED ( 'Table' ), [number of people] >=1000000 ),
        [Multipler],
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kathrynhmoss , if you have number or what if parameter for - number of people is less than X  , then you should always create a measure to ignore that. Let say if this measure you need to filter then you can do like

sumx(filter(values(Table[Institution name]), [Measure] >=100000), [Measure])

Then create a rank on this

[kathrynhmoss]

Thank you for getting back to me. I'll give this a go! 

Can i combine that measure with a ranking measure? 

[kathrynhmoss]

Thank you so much.

That has partially resolved the query. It does hide the ranking number for that instsitution, but i also need it to hide the institution name and then re-rank the institutions, for example, the institution that scores 89.5% should now show as 1, the one with 88.0% show now show as 2 etc. 

Any ideas on how i can resolve that part please? 

Thank you! 

[kathrynhmoss]

Just thought i would post some additional information as i've just realised that i've only posted half the story! 

As well as the table above i have a card in my report that is filted to one institution in particular. 

Omitting the institution that doesn't have enough people in the table is fairly easy as i can use the filters to only show items greater than X (but this still does not alter the ranks).

But i need to apply the filter to the card result too - this is where the filtering options won't work as i can only have the one measure in there. 

Apologies, i'm trying to be as clear as possible but i've been working on this issue for about 2 days now and i'm stuck! 🙂