RANK By Multiple Criteria

[bprokop]

I am trying to calculate a National Rank and State Rank using the sample data set below.  National Rank would rank Revenue by Client and Product.  State Rank would rank Revenue by Client and Product within the State.  I am looking to replicate the 4 columns to the right in Power BI using DAX.  Any help would be greatly appreciated.

						High-To-Low	Low-To-High	High-To-Low	Low-To-High
Client	State	Product	Volume	Price	Revenue	National Rank	National Rank	State Rank	State Rank
A	TX	1	4503	$23	$103,569	4	3	3	1
A	TX	2	3696	$98	$362,208	4	3	1	3
A	TX	3	8350	$50	$417,500	2	5	2	2
A	TX	4	674	$91	$61,334	5	2	3	1
A	TX	5	8497	$20	$169,940	1	1	1	3
B	CA	1	2185	$23	$50,255	5	2	2	2
B	CA	2	6522	$98	$639,156	3	4	3	1
B	CA	3	1393	$50	$69,650	5	2	2	2
B	CA	4	452	$91	$41,132	6	1	3	1
B	CA	5	8216	$20	$164,320	2	5	1	3
C	TX	1	8861	$23	$203,803	2	5	1	3
C	TX	2	2650	$98	$259,700	5	2	2	2
C	TX	3	8725	$50	$436,250	1	6	1	3
C	TX	4	6441	$91	$586,131	1	6	1	3
C	TX	5	5852	$20	$117,040	4	3	2	2
D	CA	1	9240	$23	$212,520	1	6	1	3
D	CA	2	7179	$98	$703,542	2	5	2	2
D	CA	3	7469	$50	$373,450	3	4	1	3
D	CA	4	3464	$91	$315,224	2	5	1	3
D	CA	5	6463	$20	$129,260	3	4	2	2
E	TX	1	7620	$23	$175,260	3	4	2	2
E	TX	2	330	$98	$32,340	6	1	3	1
E	TX	3	7402	$50	$370,100	4	3	3	1
E	TX	4	1805	$91	$164,255	4	3	2	2
E	TX	5	1643	$20	$32,860	6	1	3	1
F	CA	1	1312	$23	$30,176	6	1	3	1
F	CA	2	7684	$98	$753,032	1	6	1	3
F	CA	3	1349	$50	$67,450	6	1	3	1
F	CA	4	2724	$91	$247,884	3	4	2	2
F	CA	5	5109	$20	$102,180	5	2	3	1

[v-yetao1-msft · Accepted solution]

Hi @bprokop 

I created a sample with the data you provided .

Create 4 measures separately and rank them according to different categories.

National high-low = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),CALCULATE(MAX('Table'[Revenue])),,DESC,Dense)

National low-high = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),CALCULATE(MAX('Table'[Revenue])),,ASC,Dense)

State high-low = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product]) && 'Table'[State]=MAX('Table'[State])),CALCULATE(MAX('Table'[Revenue])),,DESC,Dense)

State low-high = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product]) && 'Table'[State]=MAX('Table'[State])),CALCULATE(MAX('Table'[Revenue])),,ASC,Dense)

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.