Pivot Table Total

[JonathanKelly]

I have a pivot table that counts the Areas in the columns and various calculations in the rows. One of these calculates a cost, however this is dependent on the area. I use the formula below to calculate this;

if([Area]='Wales',(Count(if([Received Date]>='$(vMonthStart)' and [Received Date]<='$(vMonthEnd)',[Received Date]))*50),

if([Area]='Scotland',(Count(if([Received Date]>='$(vMonthStart)' and [Received Date]<='$(vMonthEnd)',[Received Date]))*50),
(Count(if([Received Date]>='$(vMonthStart)' and [Received Date]<='$(vMonthEnd)',[Received Date]))*100)))

This works as intended with the individual areas however when I add the Total column using the inbuilt show totals function

The total auto calculates as if everything is multiplied by 100 rather than just a sum of all the columns. 

[JonathanKelly · Accepted solution]

Sum(aggr( sorted it.