Excluded dimensions in Pivot Table

[#1]

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

[#2]

I didn't get the question correctly, Please elaborate the problem in details

[#3]

Hi,

Basically, looking at the sample document, if you select CustomerID 5562345 the pivot shows that this Customer is connected to 5 Partners. The issue is that if I make a further selection and pick for example Product B, which only Partner A sold to the Customer, all other Partners disappear. What I need is for all 5 Partners to keep showing even if they haven't sold anything to Customer within the specific selection.

Hope this clarifies.

Thanks,

Linda

[whiteline]

Hi.

First remove your calculated dimensions, use simple PartnerID and PartnerName as second and third dimesnions.

Then add something to the Sum(sales) that is not null for other (not selected) PartnerNames and equals 1 for selected ones.

For example, =sum(Sales)*Count(distinct {<PartnerName=>} PartnerName).

Depending on your data model you can omit 'distinct' or try something similar.

There is also something to do with a total...

[#5]

Hi,

Thanks for the suggestion. I tried it but nothing changed... Partners excluded by the selections disappear anyway.

Linda

[afurtado · Accepted solution]

Hi. I just added a new expression. Maybe can help you.

Alessandro Furtado

[#7]

Thanks for the trick! It's so simple it's genius!