5

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

Sean D
  • 71
  • 1
  • 2
  • 5

2 Answers2

8

Use

=CALCULATE(MAX([UPTAKE]),FILTER(Table1,[GROUP]=EARLIER([GROUP])))
Mike Honey
  • 14,523
  • 1
  • 24
  • 40
David Hager
  • 106
  • 2
  • This worked great and introduced me to the hugely useful EARLIER function which I will continue to use extensively in this project. Many thanks. Although, replace "{" with "[" to get it working. – Sean D Feb 09 '15 at 13:09
0

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)))))))
Mohammed
  • 313
  • 1
  • 6
  • Hi Mohammed, I appreciate your response but it seems this catered towards Excel and not Power Pivot, thanks anyway though. – Sean D Feb 08 '15 at 08:51