Issue with Pivot Table and duplicated values in the raw data

[Cantrecallmyusername]

Hi,
I have a file which has ~10K rows of data.
I want to create a Pivot table removing duplicated values based on the name column this gives me ~2k but I want to capture the data that is derived in a column that contains a tag.
The problem is that when the tag is generated there are many instances where the name has 1:many generated which I loose when removing duplicates on the name.

The pivot looks like this with no changes made;

When I do a simple remove dupes based on a name value which is what is deriving the count in both pivots I get

Is there a way to represent the data that will capture my unique count on name but generate the tag count accurately - currently both versions here are incorrect to a degree
Ideally I want to have a count of how many assets are in a container (unique values) and how many tags at the different levels are outputted per container.

Is there a way to approach this is the data generation that may help?
Any help very much appreciated.

 

[GraH]

Load the data in the data model ( aka PowerPivot). Use distinct count as measure.

 

[Cantrecallmyusername]

Load the data in the data model ( aka PowerPivot). Use distinct count as measure.

Hi, thanks for the reply - how do I add distinct count as a measure in PP?

 

[GraH]

Same place where you choose between SUM, COUNT, etc in a normal pivot. In PowerPivot there is distinct count or count distinct. I never remember.

 

[Cantrecallmyusername]

Hi, thanks for the reply - how do I add distinct count as a measure in PP?

Quick google was able to sort that for me - great suggestion and it works perfectly.