Forum Discussion

Whoule's avatar
Whoule
Helper I
2 years ago
Solved

Measure returning value based on count ranking

Hello,

 

I have a table with the status of over 160K items. I want a measure to return the name of the status with the highest count. Then, I want another one for the status name of the second highest count so on and so forth until I have 5 measure for each of the 5 status I have. 

 

Thanks for any help.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Whoule

     

    You can create several measures as follows.

    count = CALCULATE(COUNT('Table'[ID]),ALLEXCEPT('Table', 'Table'[Status]))
    rank = RANKX(ALL('Table'), 'Table'[count],, DESC, Dense)
    first = CALCULATE(MAX('Table'[Status]), FILTER('Table', [rank] = 1))
    second = CALCULATE(MAX('Table'[Status]), FILTER('Table', [rank] = 2))
    third = CALCULATE(MAX('Table'[Status]),FILTER('Table',[rank] = 3))
    forth = CALCULATE(MAX('Table'[Status]),FILTER('Table',[rank] = 4))
    fifth = CALCULATE(MAX('Table'[Status]),FILTER('Table', [rank] = 5))

     

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Yuliax

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

     

     

3 Replies

    • Whoule's avatar
      Whoule
      Helper I
      2 years ago

      CoreyP 

      Here is a test sample.

      IDStatus

      1Closed
      2Free
      3Pending
      4Closed
      5Free
      6Free
      7Free
      8Free
      9Free
      10Free
      11Free
      12Free
      13Free
      14Closed
      15Closed
      16Closed
      17Closed
      18Free
      19Pending
      20Free
      21Pending
      22Free
      23Pending
      24Pending
      25Closed
      26Pending
      27Closed
      28Closed
      29Free
      30Free
      31Free
      32Free
      33Free
      34Free
      35Free
      36Free
      37Free
      38Free
      39Free
      40Free
      41Offline
      42Live
      43Free
      44Free
      45Pending
      46Pending
      47Closed
      48Live
      49Offline
      50Free
      51Free
      52Live
      53Free
      54Live
      55Free
      56Live
      57Free
      58Free
      59Pending
      60Free
      61Free
      62Live
      63Free
      64Free
      65Free
      66Free
      67Free
      68Free
      69Closed
      70Closed
      71Closed
      72Closed
      73Closed
      74Free
      75Free
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Whoule

     

    You can create several measures as follows.

    count = CALCULATE(COUNT('Table'[ID]),ALLEXCEPT('Table', 'Table'[Status]))
    rank = RANKX(ALL('Table'), 'Table'[count],, DESC, Dense)
    first = CALCULATE(MAX('Table'[Status]), FILTER('Table', [rank] = 1))
    second = CALCULATE(MAX('Table'[Status]), FILTER('Table', [rank] = 2))
    third = CALCULATE(MAX('Table'[Status]),FILTER('Table',[rank] = 3))
    forth = CALCULATE(MAX('Table'[Status]),FILTER('Table',[rank] = 4))
    fifth = CALCULATE(MAX('Table'[Status]),FILTER('Table', [rank] = 5))

     

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Yuliax

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