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. 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.

 

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 🙂

     

     

5 Replies

  • sudhav's avatar
    sudhav
    Helper V
    3 years ago

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

  • 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. 

  • sthomas38's avatar
    sthomas38
    Frequent Visitor
    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 🙂