Power Pivot: Working on corresponding values of pivot max value with row field

gifariz

Board Regular
Joined
May 2, 2021
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Hi, all. This question is related to my previous question, but a bit more complex.
I am working on data more than 1 million rows, so I need to do this in Power Pivot.
The screenshot below is an example on how to do it in normal excel sheet.

Let's say I have 3 columns: A, B, C; and there are 3 unique values in column A (let's call this Label). I can get column F = maximum values of column B for each Label (i.e. Pivot table with A as rows and max B as output values). There are multiple cells in column B that equals to maximum value in each Label. So, for each Label again, I need to get minimum values of column C that corresponding to maximum value of B (column F). Sorry my explanation is confusing, please just see the screenshot.

I can do this in normal sheet for example by using G2 = MIN(FILTER(C:C,B:B=F2)). But I don't know how to do it in Power Pivot.
In my previous question, someone give solution by using =CALCULATE(MIN('Table'[C]),FILTER('Table','Table'=MAX('Table'))). But this time, I have kind of filter inside filter.
Any suggestion? Thank you in advance.
 

Attachments

  • Capture.JPG
    Capture.JPG
    17.2 KB · Views: 73

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sorry the screenshot above was wrong. I cannot find the edit button. Here is the correct one.
 

Attachments

  • correspondingvalue.JPG
    correspondingvalue.JPG
    58.2 KB · Views: 104
Upvote 0
It is solved. For those who need, I found it can be done in Power Query:

But it would be nice if there is a way in Power Pivot also.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

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
Back
Top