Forum Discussion
Applying pivot and combining few of its columns
1. Have a dataset below, when I apply pivot I want that last three columsn get combined as in final result, is it possible?
| Customer Name | Manufacturer | Item | Order Date | Bill Date | Amount |
| Robert | Newway | Apple | 15-07-22 | 22-07-22 | 1500 |
| Jass | Highend | Orange | 10-12-21 | 17-12-21 | 4000 |
| Robert | Newway | Apple | 07-01-20 | 14-01-20 | 1500 |
| Paul | xxx | Pear | 02-07-22 | 09-07-22 | 2999 |
| Jane | xxx | Banana | 10-07-22 | 22-07-22 | 2999 |
| Jane | xxx | Banana | 01-03-22 | 08-03-22 | 5 |
| Jane | xxx | Banana | 01-12-21 | 11-12-21 | 5 |
2. Final result, should be something like this:
| Customer Name | Manufacturer | Item | Consolidated data |
| Robert | Newway | Apple | order: 15-07-22, bill: 22-07-22, qty: 1500 order: 07-01-20, bill: 14-01-20, qty: 1500 |
| Jass | Highend | Orange | order: 10-12-21, bill: 17-12-21, qty: 4000 |
| Paul | xxx | Pear | order: 02-07-22, bill: 09-07-22, qty: 2999 |
| Jane | xxx | Banana | order: 10-07-22, bill: 22-07-22, qty: 2999 order: 01-03-22, bill: 08-03-22, qty: 5 order: 01-12-21, bill: 11-12-21, qty: 5 |
10 Replies
Thanks for inputs.
1. When i try to put filter on pivot table, and arrange in ascending order for either name, manufacturer or item, consolidated columns goes away
before filter
After filter
2. ALso whats the formula applied to combined item?
vt_excel That doesn't happen for me.
In Power Pivot, I added a calculated column that combines the three relevant columns and then I created a DAX measure that allows you to include a text in the value field of the pivot table. Press this button
on the Power Pivot ribbon to get into Power Pivot / Data Model.
Now, if you are unfamiliar with Power Pivot and DAX, it's going to be challenge to just "hop on" and expect that everything will be clear at once.
