Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 744
Sean D Avatar asked Feb 07 '15 16:02

Sean D


1 Answers

Use

=CALCULATE(MAX([UPTAKE]),FILTER(Table1,[GROUP]=EARLIER([GROUP])))
like image 60
David Hager Avatar answered Nov 14 '22 21:11

David Hager