Total Function with IF statement and Variable in Pivot tables

[george456]

Dear Community

I have a questions regarding the following expression:

=if(vMeasure='[Value Sold]',$(vTYYTDValue),if(vMeasure='[Cases Sold]',$(vTYYTDCases),$(vTYYTDUnits)))/
if(vMeasure='[Value Sold]',(sum(TOTAL($(vTYYTDValue)))))

What I am trying to do is create a % Contribution in a pivot table. The Expression editor doesnt find fault but I am not getting any output.

Is this expression plausible seeing that I have so many variables already

Please advise

Regards

[Kushal_Chawda]

what is the expression in vTYYTDValue variable?

[george456]

Hi the expression is a s follows:

SUM(IF(Year2Date(MonthYr,0,1,vMaxMonth),[Value Sold]))

Regards

[Kushal_Chawda]

try this

Pick(match(vMeasure,'[Value Sold]','[Cases Sold]')+1,
$(vTYYTDUnits),$(vTYYTDValue),$(vTYYTDCases))/

SUM(total IF(Year2Date(MonthYr,0,1,vMaxMonth),[Value Sold]))

[george456]

Thanks this is perfect. One more question regarding this :

how would I do the syntax if I want the below to also be dependent on selection. You see the Units should be divided by Units, cases by cases etc..Hope this make is clear

SUM(total IF(Year2Date(MonthYr,0,1,vMaxMonth),[Value Sold]))

 Thanks for assistance thus far. Much appreciated

Regards

[Kushal_Chawda · Accepted solution]

If I understood correctly

Pick(match(vMeasure,'[Value Sold]','[Cases Sold]')+1,
$(vTYYTDUnits),$(vTYYTDValue),$(vTYYTDCases))/

Pick(match(vMeasure,'[Value Sold]','[Cases Sold]')+1,
SUM(total IF(Year2Date(MonthYr,0,1,vMaxMonth),[Unit Sold])),
SUM(total IF(Year2Date(MonthYr,0,1,vMaxMonth),[Value Sold])),
SUM(total IF(Year2Date(MonthYr,0,1,vMaxMonth),[Case Sold])))

[george456]

Hi 

Brilliant spot on. I just had to make one change and its working. Must say I am a bit unfamiliar with the Pick and match functions seems very powerful.

Regards

[Kushal_Chawda]

Yes. It is always recommended to use Pick(match( ....instead of nested if