Forum Discussion

swaroopkumarmg's avatar
swaroopkumarmg
Icon for Helper II rankHelper II
4 years ago

top 5 Profiles within category based on Profile count

Hello there, 

 

Im trying to create a Clustered chart with top n profiles.

 

Sample data: 

As you can see i want to count the number of profiles within Org Level 3 and rank them based on the count. 

 

using this measure i want to use the Top n filter to filter the top 5 profiles in a clustered column chart.

Need Help

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Count Profit name: = 
COUNTROWS( Data )

     

     

    Rating measure: =
IF (
    ISINSCOPE ( Data[Profile name] ),
    RANKX ( ALLEXCEPT ( Data, Data[Org Level 3] ), [Count Profit name:],, DESC )
)

     

    • swaroopkumarmg's avatar
      swaroopkumarmg
      Icon for Helper II rankHelper II
      4 years ago

      Thank you so much for the reply,

       

      I tried using your code but the result is just 1s. 

      i came across another code:

       

      rank = 

      IF(ISINSCOPE(data[profile name],

      RANKX(

                 CALCULATETABLE( VALUES(data[profile name],
                                              ALLSELECTED(Sheet1[profile name])

                 ), [count profile name] ))

      this works better, but this again fails when I include a date filter and set it to a particular period. 

      i.e. will the full set of data Rank measure is working fine. but when I select period the Ranking is wrong. 

       

      any way i can make it work even when a date range is selected?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Could you please share your sample pbix file? (Onedrive, Googledrive, Dropbox, ... ).