calculation not working with hidden column or with symbol atted

[rcorcoran]

Hi

I am using this expression in a pivot table to show a value.  I have the '<' in as I need the target to show as <5% etc.  This will show fine in the column I am using this expression in but I am also using the column to calculate a variance and it will not work because of this '<'

Expression in my Target YTD Column that i use for variance calculation below also:

if(ReferredYYYY=2016,'<' & num(avg({$<TargetCode={'PC31'},TargetReferredYYYY={2016}, TargetReferredMM={"<=$(=vCurrentMonth)"}>}[Target])/100,'##0%'),

Variance Calculation on columns:

([Reported

Activity YTD] - [Target  YTD  Calc])/[Target  YTD  Calc],

I have tried to put it in another column without the '<' and hide this column but when I hide the column the expression in the variance will not work.

Any Help would be appreciated

[sunny_talwar · Accepted solution]

May be use a dual function for the column:

If(ReferredYYYY=2016,

Dual('<' & Num(Avg({$<TargetCode={'PC31'},TargetReferredYYYY={2016}, TargetReferredMM={"<=$(=vCurrentMonth)"}>}[Target])/100,'##0%'),

Avg({$<TargetCode={'PC31'},TargetReferredYYYY={2016}, TargetReferredMM={"<=$(=vCurrentMonth)"}>}[Target])/100))

[rcorcoran]

Thank you so much Sunny this works fine

Rhona