Rank per product number?

[nbrandborg]

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

[CNENFRNL · Accepted solution]

[nbrandborg]

Thank you CNENFRNL!
It did the job as expected. 

Best regards!

[richbenmintz]

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])

[nbrandborg]

Hi richbenmintz,

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

Best regards!

[richbenmintz]

Hi @nbrandborg ,

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