Forum Discussion

sthomas38's avatar
sthomas38
Frequent Visitor
3 years ago
Solved

Distinct values for a Ranking measure

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. ...
Tips and Tricks
  • sthomas38's avatar
    sthomas38
    3 years ago

    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 🙂

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
sthomas38
Frequent Visitor
3 years ago

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.