Distinct values for a Ranking measure

[sthomas38]

Hi All,

I am trying to get a calculated measure for rank. When I use SKIP and DENSE, I do not get what I expect.

My data has the following fields, Network, Provider ID, State, MSA, County and Zip. I need to get a rank measure of the network by Provider Count when filtered by State, MSA, County and Zip. It is a very simple requirement, but I couldn't find this any of the forums.

Please help. Thank you in advance for your time and help.

[sthomas38 · Accepted solution]

I found a solution from youtube - alphabetical rank calculation. 

Create a measure such as this -

Alphabetic Rank =

VAR _curNetwork = SELECTEDVALUE(Table[Network])

VAR res = COUNTROWS( FILTER( ALL(Table[Network]), Table[Network] <= _curNetwork ) )

RETURN res  

The next rank measure calculation is - 

RANK_ALL = RANKX( ALL(Table[Network]), [Provider Count] + DIVIDE([Alphabetic Rank],100), ,DESC ) 

This works very well - I am able to filter by State, MSA, Region, County and Zip just by using this one calculation. 

Thank you 🙂

[v-xinruzhu-msft]

Hi @sthomas38 

You can refer to the following example.

Create a new measure

Measure = RANKX(FILTER(ALL('Table'),[State]=MAX([State])),CALCULATE(SUM([PROVIDER COUNT])),,ASC,Dense)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sthomas38]

Hi, thank you for responding. So I will have to create one measure for each filter, right? (Example this will only filter ranks by state). Similarly, I will have to create another measure for MSA, County, Region and Zip? Also the visual cannot have ONE field, multiple calculated rank fields. 

[sudhav]

you can use child column i.e NETWORK to get proper ranks

[sthomas38]

I am sorry, I don't know what that means..?