Forum Discussion
Re: Calculated fields in pivot
Does this do what you want?
=IFERROR(IF(MaxInd<>0,IF(SUM(Actual/Target)>3,3,IF(SUM(Actual/Target)>1.5,1,5,SUM(Actual/Target)))),0)
6 Replies
Hi,
Thanks for the above. This is working for the rows that have a MaxInd value.
Where the MaxInd value is 'No', I'd like the actual result of Actual/TargetHow can I achieve this?
Attahced are the results for what I desire to achieve (image one) and what the new formula currently outputs (two).
So in essence, I'd like teh formula to display what is shown in red in the image one.
Please assist,
Thanks
Did you try measure instead of calculated column?
I'm afraid I don't really understand. Does this do what you want?
=IFERROR(IF(MaxInd="",IF(SUM(Actual/Target)>3,3,IF(SUM(Actual/Target)>1.5,1,5,SUM(Actual/Target))),SUM(Actual/Target)),0)
Hi,
Apologies for the confusion. The spreadsheet is also making use of a Calculated Item (image MaxInd). So, on the pivot, the data pulls as shown in Desired image.
I would like the formula to work out the values in red (image Desired).
Please assist
Thanks
