getting data to show in expanded chart

[rcorcoran]

Hi I have a target in a chart that i want to show when the pivot table is expanded by one dimension.  The expression I have is:

if(ReferredYYYY=2016,if(Dimensionality()=1,num(Sum ({<TargetCode={'OP8'},TargetReferredYYYY={2016},TargetReferredMM={"$(=vCurrentMonth)"}>}[Target]),'#,##0')),

if(ReferredYYYY=2015,'',

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))),

This is only showing when the table is colapsed however if I hardcode the figure in it will work as below:

if(ReferredYYYY=2016,if(Dimensionality()=1,'23,454'),

if(ReferredYYYY=2015,'',

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))),

Any clues as to how I can get this to work with the num(sum expression for the data after the dimensionality?

Thanks

[sunny_talwar · Accepted solution]

May be try this:

If(ReferredYYYY=2016, If(Dimensionality() = 1, Num(Sum(TOTAL {<TargetCode = {'OP8'}, TargetReferredYYYY = {2016}, TargetReferredMM = {"$(=vCurrentMonth)"}>} [Target]),'#,##0')),

If(ReferredYYYY=2015,'',

If(ReferredYYYY=2014, '',

If(ReferredYYYY=2013, ''))))

[rcorcoran]

Brilliant Sunny T that works fine