Power Pivot - aggregate within groups to determine max value

[waealu]

I'm looking for a formula (for Power Pivot) that aggregates within certain groups and across other groups to determine the maximum.

Here's my data table:

State	Customer	Fruit	Qty
NY	A	Apple	5
NY	A	Orange	1
NY	A	Pear	5
NY	B	Apple	1
NY	B	Orange	6
NY	C	Apple	2
NY	C	Orange	2
NY	C	Pear	5
CA	D	Orange	4
CA	D	Pear	2

I want to determine the most popular fruit by `State` (ignoring `Customer`). In NY, there are a total of 8 apples, 9 oranges, and 10 pears. So the formula should return `Pear`.

Resulting in a table like this:

State	Dominant Fruit
NY	Pear
CA	Orange

What is the Power Pivot formula I need for that `Dominant Fruit` column on the resulting table? Thanks

 

[Alex Blakenburg]

Have you considered using Power Query ? This is really easy using Power Query to feed Power Pivot.

20210226 Power Pivot PQ Top 1.xlsx
	A	B	C
1
2
3	State	Fruit	Sum of Qty
4	CA	Orange	4
5	NY	Pear	10
6	Grand Total		14
Pvt Top 1