Show all values (incl. null) in pivot table

[Anonymous]

Hi all,

I have a pivot table as shown below

I have a total of 5 possible values for the 'Overdue' field - < 3 Months, >= 3 Months, >= 6 Months, >= 1 Year and >= 2 Years

I would like to display all 5 values for each Dept.

Have tried using the following expressions for the measure to force show all values but to no avail

-if(Sum({1} [2nd dimension]) > 0, Sum([Sales]),0)

//force show all values even if Dept has 0 values

- if (sum({Ignore All dimensions} Sales) <> sum(Sales), sum({Your set analysis} sales, 0)

//from Show all values in Pivot/Straight Table (irrespective of current selection)

My current expression for the measure is:

if(Count({1} [Overdue]) > 0,

     if(Count({$<[VA.Month]={'$(=Max([VA.Month]))'}, varMonth={"0"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 2nd mth?"={'Yes'}, varMonth={"1"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 3rd mth?"={'Yes'}, varMonth={"2"}, [VA.isOverdue]={'Yes'}>}

            distinct [%ItemID])='0', Null(), //if equals 0 then Null else count items

Count({$<[VA.Month]={'$(=Max([VA.Month]))'}, varMonth={"0"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 2nd mth?"={'Yes'}, varMonth={"1"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 3rd mth?"={'Yes'}, varMonth={"2"}, [VA.isOverdue]={'Yes'}>}

            distinct [%ItemID])), 0)

For context, attached is majority of the load script for this table. (Deleted some fields which are not used in this pivot table)

Any idea what could be the cause/how to display all values? TIA!

-Serena