Log In
Register
Home
December 29, 2016
Offline
Hi all,
I created a dashboard but when I summarize information in a pivot table there are some mistakes.
Example:
I have Column A with Contries : Portugal, Brasil, Costa Rica... and Column B with respective regions: Lisbon, São Paulo... for Portugal the correspondence is only Lisbon in all rows, but when filter Portugal in a pivot appear all options in Region. How do you solve that?
Example in attached. The similar exercise is for a brand. For example, I have brand A, with sub brands AA, AAA, AAAA and in the list I have other brand and subrand B - BB, BBB, BBBB... when I filter by A , I want that sub brand appear only AA, AAA, AAAA...
Thanks 🙂
VIP
April 21, 2015
Offline
Well in fact it is working correct I think. The outcome (28) is correct.
So it has to do something with the way you present it.
When I make a 'normal' pivottable, and filter on Portugal, it only shows Lisboa under it.
Don't know how you made your pivottable and if it is possible to change it to a more normal format?
VIP
Trusted Members
June 25, 2016
Offline
Hi Hava/Frans
I think what was required is not the result.
When filtering Country, Hava wanted the Region filter to only show what is related to the Country filter only.
i.e. if Portugal is filtered then the Region filter should only contain Lisboa and not other regions (currently it is showing other regions not related to Portugal). I don't think this is possible in Excel 2010.
If you use Excel 2013 and you have slicers, then right click on the slicer, choose the "Slicer Settings" option and the tick the box that says "Hide items with no data".
Hope this helps.
Sunny
VIP
April 21, 2015
Offline
In my example (shown above) you see only Lisboa when you filter on Portugal.........
VIP
Trusted Members
June 25, 2016
Offline
See if any of this helps.
Filter1 is a close as I can get.
Filter2 would be perfect, ONLY if there is one country-one region.
Both require a helper column.
Sunny
1 Guest(s)
