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

5
votes

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

2
excelpowerpivotdax

2 Answers

8
votes

Use

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

use this formula in cell ("C2"):

=MAX(INDIRECT(CONCATENATE("B",MATCH(A2,$A$1:$A$10,0),":B",SUMPRODUCT(MAX(($A$1:$A$10=A2)*(ROW($A$1:$A$10)))))))