Sum together the maximum of two measures (by dimension)

[craggus]

Afternoon,

I have a basic table showing budget and actual spend against a list of activities. What I would like to do is to create a single KPI showing 'expected' spend - this would sum together the maximum value of either budget or actual PER ACTIVITY. In the example below it would sum the purple cells, resulting in a KPI of 128,000 (i.e. 30,000 + 30,000 + 20, 000 + 12,000 + 21,000 + 15,000)

What formula would I use to calculate this?

Many thanks

Craig

[maxgro · Accepted solution]

Try with

SUM(AGGR(RANGEMAX(SUM(Budget), SUM(Actual)), [Activity ID]))

[craggus]

Hi Maxgro,

Fantastic! Works exactly as required.

Many thanks for your help, much appreciated...

Craig