6 Replies Latest reply: Nov 24, 2013 8:21 AM by Linda Diodato RSS

    Excluded dimensions in Pivot Table

    Linda Diodato

      Hello,

       

      I have a series of CustomerIDs and each of them is linked to several PartnerIDs along with the related Sales by Product. I have a pivot table where my first dimension is CustomerID and as a second dimension I need to show ALL the Partners that have transacted with these Customers in the past regardless of any other selection. I appreciate the sum(Sales) expression would return a '0' in such cases and that's fine as long as all the Partners are visible. On the other hand, CustomerIDs showing in the chart should be indeed driven by the selections.

       

      I've attempted several expressions for the Partners but none worked, not even the one below which looks fine to me:

       

      =aggr(if(sum({1} Sales)>0,PartnerID),PartnerID,CustomerID)

       

      Can somebody help please?

       

      I've attached a sample document.

       

      Thanks,

      Linda