I am building a data model within Power Pivot for Excel 2013 and need to be able to identify the max value within a column for a particular group. Unfortunately what I thought would work and what I have searched for previously gave me an error or wasn't applicable (there was a similar question that dealt with calculated measures rather than columns and wasn't replicable in Power Pivot data view to the best of my knowledge)
I have included an indication of what I am trying to achieve below, in this case I am trying to calculate the Max % uptake column.
Group | % uptake | Max % uptake
A 40 45
A 22 45
A 45 45
B 12 33
B 18 33
B 33 33
C 3 16
C 16 16
C 9 16
Many thanks
Use
=CALCULATE(MAX([UPTAKE]),FILTER(Table1,[GROUP]=EARLIER([GROUP])))