Top 10 vs Slicer/Filter

[Maddy97]

Hi there!

I am trying to create a dashboard with multiple pivot tables. I want them all to display the top 10 in their field (ie top 10 clients by total value). I can get this to work, but when I add a slicer on top of this (eg top 10 clients by total value in Yorkshire) it seems to lose the top 10 filter, the pivot table grows/shrinks and I often get the error that the tables cannot overlap. I get the feeling from research that I cant achieve what I want, but I thought id ask before I gave up!

Thanks in advance.

[Richard Buttrey]

Hi, and welcome to the forum

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Maddy97]

So I think I have attached a screen shot. Im not sure how to upload the excel file without giving all the data away! Please let me know if you need more!

[Richard Buttrey]

Take a copy of the workbook and remove all but a representative sample of your data, we don't need zillions of rows
Then anonymise the rest of the names. We don't mind how many Michael Mouses or Donald Ducks, or even D Trumps you have.
Then upload the workbook making sure you have manually added the results you expect.

[Maddy97]

Ok, so I have actually found a reasonable solution to that problem, but I have another if you are willing to help? I have a bunch of accounts that have between 1 and 4 clients each. the client names are all stored in the same cell. I want to return the total value of the clients accounts, including those where they collaborate but the pivot table only counts those where they are the sole account holder. in the basic example I have attached, I would like the table to read A - £35, and B - £20. is this achievable??
Thanks in advance.

[JeteMc]

Perhaps the following will help.
The clients list is parsed into columns C:F using: =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(COLUMNS($A$1:A$1)-1)*LEN($A2)+1,LEN($A2)))
The total for each client is displayed using: =SUMPRODUCT((C$2:F$10=H2)*(B$2:B$10))
For future reference please open a new thread when you have a question that is not just a follow up on the original.
Let us know if you have any questions.