We have a pivot table with a date dimension along the top.

We have a row which is populated by a 1 or a 0 by comparing the sum of the service level (either a 1 or 0) to the number of working days in order to only count those working days which are 1 and where there was not a previous value of 0. We want to then find the sum of these 1s and put this in our total column.

The calculation we are using for the row is:

if(weekdayRMflag=0,0,if(Rangesum(Before(Sum(ServiceLevel),0,NoOfColumns()))=Rangesum(Before(Sum(weekdayRMflag),0,NoOfColumns())),1,0))

In order to make the total work (just using the pivot table's own total doesnt work) I;ve tried to use:

=if(ColumnNo()=0,

Sum(Aggr(after(rangesum(if(weekdayRMflag=0,0,if(Rangesum(Before(Sum(ServiceLevel),0,NoOfColumns()))=Rangesum(Before(Sum(weekdayRMflag),0,NoOfColumns())),1,0))),1,NoOfColumns()), planningdiaryname, ECEngID, ECEng, ECEngType))

,

if(weekdayRMflag=0,0,if(Rangesum(Before(Sum(ServiceLevel),0,NoOfColumns()))=Rangesum(Before(Sum(weekdayRMflag),0,NoOfColumns())),1,0))

)

in order to make it do something different with the total row which is the first column but this is not working.