Hi I have an expression in a pivot table as follows. My problem with it is for the first expression where it is adding two figures (PallCare Paeds 1 =1 and PallCare Paeds 2 = 1 it is not showing up if one of them has no value. It will show up if they both have values in. I need it to show the value if only one has a value or if both do.
Any help appreciated
if(isnull(Pick(Match(ValueList($(vPallCareChildren)),$(vPallCareChildren)),
num((Sum({$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue)) + (Sum({$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue)), '#,##0'),
num(Avg({$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue), '#,##0'),
num(Avg({$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue), '#,##0'),
num((Avg({$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue)) + (Avg({$<[PallCare Paeds2]={5},ReferredMM={1}>}ActualValue)) +(Avg({$<[PallCare Paeds2]={72},ReferredMM={1}>}ActualValue)), '#,##0'),
num(Avg({$<[PallCare Paeds2]={5,72},ReferredMM={1}>}ActualValue), '#,##0'),
num(Avg({$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue), '#,##0'))),Null(),
Pick(Match(ValueList($(vPallCareChildren)),$(vPallCareChildren)),
num((Sum({$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue)) + (Sum({$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue)), '#,##0'),
num(Sum({$<[PallCare Paeds2]={1},ReferredMM={1}>}ActualValue), '#,##0'),
num(Sum({$<[PallCare Paeds1]={1},ReferredMM={1}>}ActualValue), '#,##0'),
num((Sum({$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue)) + (Sum({$<[PallCare Paeds2]={5},ReferredMM={1}>}ActualValue)) +(Sum({$<[PallCare Paeds2]={72},ReferredMM={1}>}ActualValue)), '#,##0'),
num(Sum({$<[PallCare Paeds2]={5,72},ReferredMM={1}>}ActualValue), '#,##0'),
num(Sum({$<[PallCare Paeds1]={5},ReferredMM={1}>}ActualValue), '#,##0')))