Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |