Applying pivot and combining few of its columns

Occasional Contributor

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 NameManufacturerItemOrder DateBill DateAmount
RobertNewwayApple15-07-2222-07-221500
JassHighendOrange10-12-2117-12-214000
RobertNewwayApple07-01-2014-01-201500
PaulxxxPear02-07-2209-07-222999
JanexxxBanana10-07-2222-07-222999
JanexxxBanana01-03-2208-03-225
JanexxxBanana01-12-2111-12-215

 

 

2. Final result, should be something like this:

Customer NameManufacturerItemConsolidated data
RobertNewwayAppleorder: 15-07-22, bill: 22-07-22, qty: 1500
order: 07-01-20, bill: 14-01-20, qty: 1500
JassHighendOrangeorder: 10-12-21, bill: 17-12-21, qty: 4000
PaulxxxPearorder: 02-07-22, bill: 09-07-22, qty: 2999
JanexxxBananaorder: 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

@vt_excel That could be with Power Pivot. Demonstrated in the attached file.

Riny_van_Eekelen_0-1673777613537.png

 

@Riny_van_Eekelen 

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

 

vt_excel_2-1673788583190.png

 

 

vt_excel_1-1673788396052.png

 

 

After filter 

vt_excel_0-1673788372626.png

 

 

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

Riny_van_Eekelen_0-1673794020619.png

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.

@Riny_van_Eekelen Hi

I tried similiar thing, but I got error this, can you please advise.

Attached is excel where I am working

 

vt_excel_0-1674308074588.png

 

 

 

@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 markalt-Enter, quotation mark.

 

The measure will than look like this:

Riny_van_Eekelen_0-1674311631452.png

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.

 

@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

image.png

Please see attached.

@Sergei Baklan @Riny_van_Eekelen 

Thanks for help, not able to do

 

 

@vt_excel 

Does file attached to my post work with you? Perhaps you may share your sample file to check what is wrong.

HI @sergei,
Sorry Typo, file is working
had to write *now instead of not

thanks for help

@vt_excel , you are welcome, thank you for the update