Jan 15 2023 01:53 AM
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 |
Jan 15 2023 02:13 AM
Jan 15 2023 05:17 AM
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?
Jan 15 2023 06:52 AM
@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.
Jan 21 2023 05:35 AM
I tried similiar thing, but I got error this, can you please advise.
Attached is excel where I am working
Jan 21 2023 06:40 AM
@vt_excel It seems you are using an older version of PQ that doesn't recognize the UNICHAR function.
Replace UNICHAR(10) with the following key sequence:
quotation mark, alt-Enter, quotation mark.
The measure will than look like this:
Now you can drag the measure "d" into the Values field of the Pivot table. Make sure that the part of the pivot table that holds this measure is set to Wrap Text.
Jan 21 2023 06:45 AM
@vt_excel
DAX doesn't support CHAR() functions, and for not-beta doesn't support UNICHAR(). Try to Alt+Enter here
d:=VAR textCombined = CONCATENATEX(Table1, Table1[consolidate], "
") RETURN IF(HASONEVALUE(Table1[Customer Name]), textCombined, "")
in concatenatex() between "". RETURN is adjusted to to show all texts combined in Grand Total.
Don't forget to wrap text in cell formatting for PivotTable
Please see attached.
Jan 22 2023 04:50 AM
Jan 24 2023 07:56 AM
Does file attached to my post work with you? Perhaps you may share your sample file to check what is wrong.
Jan 25 2023 08:49 PM
Jan 25 2023 11:25 PM
@vt_excel , you are welcome, thank you for the update