RANK SET ANALYSIS

[KevinJ]

I need to show the top 10 using rank with a set analysis. I just need it to focus on the item type : inventory Item.

Goal : 

1. Rank show only top 10. Rank by Subtotal

2. Colums : Item Name, Subtotal, Total Unique Purchase Orders.

Item Name	Subtotal	PO	Item Type
Product 1	500	1	Inventory Item
Product 2	250		Service
Product 1	500	1	Inventory Item
Product 2	250	2	Inventory Item
Product 2	250	1	Inventory Item
Output:
Rank	Item Name	TOTAL PO	Total
1	Product 1	2	1000
2	Product 2	3	500

[MayilVahanan · Accepted solution]

Hi

Try like below

If rank is based on item Name, then in Aggr function, instead of subtotal, try like below

=Aggr(Rank(Sum(Subtotal)), [Item Name])

[KevinJ]

I forgot to include the ranking would also be a set analysis of Currency = PHP, and Item Type Inventory Item. How would that be possible? 

Item Name	Subtotal	PO	Currency	Item Type
Product 1	500	1	PHP	Inventory Item
Product 2	250		EUR	Service
Product 1	500	1	PHP	Inventory Item
Product 2	250	2	EUR	Inventory Item
Product 2	250	1	EUR	Inventory Item
Product 1	5000	2	EUR	Inventory Item
Output:
Rank	Item Name	TOTAL PO	Total
1	Product 1	2	1000
2	Product 2	3	500

[KevinJ]

I forgot to include the ranking would also be a set analysis of Currency = PHP, and Item Type Inventory Item. How would that be possible? 

Item Name	Subtotal	PO	Currency	Item Type
Product 1	500	1	PHP	Inventory Item
Product 2	250		EUR	Service
Product 1	500	1	PHP	Inventory Item
Product 2	250	2	EUR	Inventory Item
Product 2	250	1	EUR	Inventory Item
Product 1	5000	2	EUR	Inventory Item
Output:
Rank	Item Name	TOTAL PO	Total
1	Product 1	2	1000
2	Product 2	3	500