Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
Thank you so much Sunny this works fine
Rhona