cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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?

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Ranking by group

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Frequent Visitor

Re: Ranking by group

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 😞
Highlighted
Super User IV
Super User IV

Re: Ranking by group

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors