PowerPivot: How to identify Max Value per Group in a Calculated Column

Sean D picture Sean D · Feb 7, 2015 · Viewed 10.4k times · Source

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

Answer

David Hager picture David Hager · Feb 8, 2015

Use

=CALCULATE(MAX([UPTAKE]),FILTER(Table1,[GROUP]=EARLIER([GROUP])))