Forum Discussion
Solved
Applying pivot to get total value per name, but add items name only for unique
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
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
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
- 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- 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.
