Forum Discussion

dojobrady's avatar
dojobrady
Frequent Visitor
6 years ago

Ranking by group

Ok here is my issue:

I have a raw data set that looks something like this. Basically, I'm looking at inventory of products, and "title" of products is duplicated and primarykey is a unique identifier for the item itself. See below:

Title    PrimaryKey  
A            1
B            2
A            3
A            4
A            5
B            6
B            7
B            8
A            9

What I am trying to create is something that would rank A and B by distinct counts of the primarykey, showing:
Title     Rank
A            1
B            2

The purpose and context is simple: I am looking at item level sales for the past three months, but I want to show the rank of the unique SKUs we have sold based on the distinctcount of units. I have tried creating a calculated table with title, but I can't get the distinctcount of units to work in there. Any ideas?

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey,

     

    first I created this measure:

    count pk = COUNT('Table'[Primary Key] )

    This measures just counts.

     

    Then I created the measure that ranks the available Titles by the measure count pk like so:

    rankx title by cnt pk = 
RANKX(ALLSELECTED('Table'[Title]) , [count pk] , , DESC , Dense)

    This allows to create a simple table visual like this:

    if you wan to suppress the value for the rank measure at the Total level just use the measure like so:

    rankx title by cnt pk = 
IF(HASONEVALUE('Table'[Title])
,RANKX(ALLSELECTED('Table'[Title]) , [count pk] , , DESC , Dense)
,BLANK()
)

    Hopefully this provides what you are looking for.

     

    Regards,

    Tom

    • dojobrady's avatar
      dojobrady
      Frequent Visitor
      6 years ago
      SKU Rank = IF(HASONEVALUE('Sales'[Title])
      ,RANKX(ALLSELECTED('Sales'[Title]),[Units],,DESC,Dense)
      ,BLANK()
      )
       
      Gives me no value when I put Title and SKU rank next to one another in a visual :(
      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        6 years ago

        Hey,

         

        as it seems the sample data does not reflect your data model, as both measures I provided worked with the sample data you provided.

         

        Please consider to provide a pbix file that contains sample data and represents your data model. If you have used xlsx files to create the sample data provide these files as well. Upload the files to onedrive or dropbox and share the link.

         

        Regards,

        Tom