cancel
Showing results for
Did you mean:
Helper I

## Rank per product number?

Hi all

I have a dataset with three columns with data (a-c) and one measure (d) a percentage of total sales per product number.

However if I want to pull up the rank for the individual Countries per Product Number in a seperate column, is there an easy way to do this?
If similar procentage value then it should have same ranking.

I reckon it a RANKX based on the condition of the product number and sales per country.

The expected outcome should be like this:

 Product number Country Sales Percentage Rank 111 Denmark 100 16,67% 3 111 Norway 150 26,66% 2 111 Sweden 250 40,00% 1 111 Finland 100 16,67% 3 222 Finland 200 25,00% 2 222 Iceland 200 25,00% 2 222 Sweden 400 50,00% 1 333 Denmark 100 20,00% 2 333 Sweden 400 80,00% 1
1 ACCEPTED SOLUTION
Super User

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
5 REPLIES 5
Super User

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

Thank you CNENFRNL!
It did the job as expected.

Best regards!

Super User

Hi @nbrandborg ,

you can create the following measures:

``````Pct = DIVIDE(sum('Table'[Sales]),  CALCULATE(sum('Table'[Sales]), ALLEXCEPT('Table','Table'[Product number])))

Rank = RANKX(ALLEXCEPT('Table','Table'[Product number]), [Pct])
``````

I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!

Helper I

Hi richbenmintz,

Unfortunately it didn't work. It came with a "circular dependency" error.

Best regards!

Super User

Hi @nbrandborg ,

I am guessing you tried to create calc columns and not measures

I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!

Announcements