Power Pivot - aggregate within groups to determine max value

waealu

New Member
Joined
Feb 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

StateCustomerFruitQty
NYAApple5
NYAOrange1
NYAPear5
NYBApple1
NYBOrange6
NYCApple2
NYCOrange2
NYCPear5
CADOrange4
CADPear2

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:

StateDominant Fruit
NYPear
CAOrange

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,688
Office Version
  1. 365
Platform
  1. Windows
Have you considered using Power Query ? This is really easy using Power Query to feed Power Pivot.

20210226 Power Pivot PQ Top 1.xlsx
ABC
1
2
3StateFruitSum of Qty
4CAOrange4
5NYPear10
6Grand Total14
Pvt Top 1


1614338317396.png


1614338276579.png
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top