Forum Discussion
Pivot table-filters?
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
Dear Sergei
I am facing a difficulty in the second step. I use Excel 2007. How can create a measure?
Sophia
Oops... I don't remember how it was in 2007. In new version two steps:
1) Add table to data model
2) Right click on Table name and select add measure
I even don't remember if such kind of measures (with DAX formulas) are supported in 2007 or not...
As workaround you may add helper column to your table which shows how many times names are repeated (if > 1 when they are in more than one group)
=SUMPRODUCT(([Name]=C4)*1)
where C4 if first cell in name column, copy it down on entire column.
Create pivot table (i didn't add it to data model), take Helper as the filter for pivot table, and then apply filters
Helper = 1
Groups label = HHH