Forum Discussion
Solved
Pivot table-filters?
Hi all
Let's say that I have some data and I create a pivot table like the following:
I want to "filter" (isolate) the customers that have only the HHH product group. Not any other customer that has HHH AND something else. Is there any way that I can do that?
Thank you so much in advance!!!!
Hi Sophia,
Please find attached the file with my sample if it works in your environment. In your case instead of [Name] in the formula you may use the range like $A$3:$A$13, be sure to use absolute reference.
Hi Sophia,
Stay on any cell with Group within your pivot table, when by right click menu or from drop down menu on the top of your pivot table (cell C2 in your case) select Label Filters and when Equals to HHH
Hi Sergei
Thank you for your response.
The problem is that if I use filters the result will be this:
As you can see all customers that have the HHH product group appear. In this example, I want only the customer AAAAAAA to appear (as he is the only one with HHH as the only product group)...
Hope I helped yo understand....
Sophia,
1) Creating the pivot table be sure it's added to data model
2) Create measure GroupsPerName for your pivot table like
=CALCULATE(DISTINCTCOUNT(Table1[Group]),ALL(Table1[Group]))
it calculates how many different groups you have
3) Filter you pivot table on HHH group - you'll have few names for such group
4) Right click on the cell with any name, Filter->Value Filters->GroupsPerName=1
(measure in above is selected from drop down list)
Now you have only names who are in HHH group in no one more group
