Excel 2019: Text in the Values of a Pivot Table
June 24, 2019 - by Bill Jelen
Another amazing use for a measure in a Data Model pivot table is to use the CONCATENATEX function to move text into the values area of a pivot table.
In this data set, there is an original and revised value for each sales rep.
Insert a pivot table and check the box for Add This Data To The Data Model. Drag Rep to the Rows and Version to Columns.
The Grand Totals get really ugly, so you should remove them now. On the Design tab, use Grand Totals, Off For Rows and Columns.
In the Pivot Table Fields panel, right-click the Table name and choose Add Measure.
The formula for the measure is
=CONCATENATEX(Values(Table1[Code]),Table1[Code],", "). The VALUES function makes sure that you don't get duplicate values in the answer.
After defining the measure, drag the measure to the Values area. In this case, each cell only has one value.
However, if you rearrange the pivot table, you might have multiple values joined in a cell.
A cell may not contain more than 32,768 characters. If you have a large data set, it is possible that this Grand Total of this measure will be more than 32,768 characters. The Excel team never anticipated that a pivot table cell would contain more than this many characters, but thanks to DAX and CONCATENATEX, it can happen. When it does happen, Excel can not draw the pivot table. But - there is no error message - the pivot table simply stops updating until you get rid of the Grand Total or somehow make the largest cell be less than 32,768 characters.
Title Photo: rawpixel.com / Unsplashtw