Summing only Max Values in set Analysis

[#1]

Morning all.

I have a Set Analysis that works well when I filter the table as we only want to show the Max value for a certain Division or Site, My client now wants to include multiple Division's and want's to Sum all the max values I have tried

=SUM(aggr(MAX({$<

  [Activity Conversion Key]  ={'FloorArea'}

  ,[Activity Period Year]={$(vYearFilter)}

  >

  }

  num([Activity Value],'#,##0.000'))

But it does not work.

I have attached an excel of the data

[sunny_talwar]

What are the chart dimensions where you are planning to use this expression?

[#3]

The chart Dimension is year. It's going into a Bar chart with a Cyclic Group.

[sunny_talwar · Accepted solution]

May be try like this:

=Sum(Aggr(Max({$<[Activity Conversion Key]  = {'FloorArea'}, [Activity Period Year]={$(vYearFilter)}>} [Activity Value]), Year, Division, $(='[' & GetCurrentField(CycleGroupName) & ']')))

Where you have two dimensions in your chart -> Year and Cycle Group

[#5]

Excellent. Thank you Sunny works a treat.