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.