Applying pivot to get total value per name, but add items name only for unique

[vt_excel]

A) I have below data

Name	Value	Item
Robert	200	Apple
Robert	500	Apple
Robert	100	Orange
Austin	50	Pie
Austin	70	Grape
Austin	20	Pie

B) Want that, when applying pivot, final result should be like below

1. name column get combined, 

2. Value name gets add up

3. Item names get concatente but only unique items appears like below:

Name	Value	Items
Robert	800	Apple ; Orange
Austin	140	Pie ; Grape

c) I tried , but in items i am getting all the values & repeat also.

how can i get only unique values in it

[#2]

Hi,

You'll need to add your table to the Data Model, after which you can achieve what you want using a measure within Power Pivot. Let me know if that's an option for you and I'll post the solution.

Regards

[#3]

Hi Jos,
Yes I can add data model & measure, but I dot use power pivot.
I use normal pivot, data model & measure, is it possible with these

[#4]

Your Pivot Table has to come from the Data Model. Not sure what you mean by not using Power Pivot, since by definition any measures must be created within Power Pivot.

[#5]

Yes
can u post one solution?

[#6 · Accepted solution]

@vt_excel 

Create this measure within Power Pivot:

=
CONCATENATEX(
    VALUES( Table1[Item] ),
    Table1[Item],
    ", "
)

Change the table name as required.

You can then add this measure to the Values area of the Pivot Table together with the Name field (Rows area) and Value field (Values area). 

Regards