Ranking for separate category issue

[Kopek]

Hi!

I am trying to create a calculated column with a ranking for a values, but without any success.

The issue is that i need to create a separate ranking for each bucket (combination of unique values) within one table.

I was able to create a proper rating for entire table, but struggling with splittig that to a bucket.

The bucket for rating should be: ID, Qtr+Yr and Category.
So for each combination of unique ID, Qtr+Yr and Category value should be ranked separately, like below:

ID	Qtr+Yr	Category	Value	Rank
123	Q1 2020	Cat 1	1000	3
123	Q1 2020	Cat 1	1500	2
123	Q1 2020	Cat 1	2000	1

ID	Qtr+Yr	Category	Value	Rank
123	Q1 2020	Cat 2	2500	1

ID	Qtr+Yr	Category	Value	Rank
123	Q4 2019	Cat 1	1000	3
123	Q4 2019	Cat 1	1400	2
123	Q4 2019	Cat 1	1500	1

ID	Qtr+Yr	Category	Value	Rank expected
123	Q4 2019	Cat 2	2345	2
123	Q4 2019	Cat 2	3456	1

Below you have a high level example of my data :

ID	Qtr+Yr	Category	Value	Rank expected	Rank achieved
123	Q4 2019	Cat 3	7777	1	4
123	Q4 2019	Cat 2	3456	1	7
123	Q1 2020	Cat 2	2500	1	8
123	Q4 2019	Cat 2	2345	2	9
123	Q1 2020	Cat 1	2000	1	10
123	Q1 2020	Cat 1	1500	2	11
123	Q4 2019	Cat 1	1500	1	12
123	Q4 2019	Cat 1	1400	2	13
123	Q1 2020	Cat 1	1000	3	16
123	Q4 2019	Cat 1	1000	3	17
345	Q1 2020	Cat 1	99999	1	1
345	Q4 2019	Cat 1	11111	1	2
345	Q1 2020	Cat 1	8888	2	3
345	Q1 2020	Cat 1	7777	3	5
345	Q1 2020	Cat 2	5555	1	6
345	Q4 2019	Cat 1	1113	2	14
345	Q4 2019	Cat 1	1112	3	15
345	Q4 2019	Cat 2	45	1	18
345	Q4 2019	Cat 2	22	2	19
345	Q4 2019	Cat 3	6	1	20

Any suggestions are appreciated!

Thanks!

[CNENFRNL · Accepted solution]

For fun only,

DAX measure solution,

DAX calculated column solution,

PQ solution,

Excel worksheet formula is powerful enough,

SQL solution,