Hey everyone.
So I have this pivot table for my Market Basket report, and I can't get the results of the grand total to be the same as my target. I have tried the following code for my Total Quantity Sold expression:
if(Dimensionality()>0, if([Qty Sold w/ Selected]>0, Sum( {<PRODUCTDESC = E(PRODUCTDESC)>} QUANTITY)),
Sum(aggr(SUm({<PRODUCTDESC =E(PRODUCTDESC)>}QUANTITY),TRANSACTIONTYPE,QUANTITY,BRANCHNAME)))
to calculate the Total Quantity Sold of the products that were sold with the selected product. I believe I have the correct formula for getting the quantities needed; all I'm lacking is the correct grand total. Using the equation above I have hidden the products that have zero Quantity Sold w/ Selected values. This is my formula for Quantity Sold w/ Selected:
sum( {<TXNNUMBER = P(TXNNUMBER), PRODUCTDESC = E(PRODUCTDESC)>} QUANTITY )
so now the products hidden in my Total Quantity Sold column that have corresponding values to zero values in my Quantity Sold w/ Selected column are still included in the Grand Total. Any ideas how not to include them?
