|

Excel: Reporting Text in a Pivot Table

By Bill Jelen
July 1, 2018
21 comments

Pivot tables are excellent for summarizing numbers. At one of my Power Excel seminars recently, someone wanted to show a text field in the Values area of a pivot table. Thanks to the Data Model and the new DAX function CONCATENATEX introduced in 2017, you can build such a pivot table.

 

 

The attendee said, “I have a data set showing the prior and current status for support tickets. I want to report the text from the Status field in the Values area of a pivot table.” While the Data Model, introduced in Excel 2013, and CONCATENATEX provide a solution, these calculations are only available in Windows versions of Excel. They won’t work in Excel for Android, Excel for iOS, or Excel for Mac.

 

DAX stands for Data Analysis eXpressions. The DAX formula language is a new set of functions for creating calculated fields in a pivot table. While many of the functions are similar to the functions in regular Excel, there are several powerful additions that allow calculations previously impossible in a pivot table.

 

In order to use DAX in a pivot table, follow these steps:

 

1. Select one cell in your data set and press Ctrl+T (or go to Home, Format as Table).

2. By default, the new table will be called Table1. Click on the Table Tools Design tab in the Ribbon and assign the table a name. The table name can’t have spaces. A name such as “TicketData” would work.

3. Select one cell in the table. From the Insert Tab, choose Pivot Table.

4. In the Create Pivot Table dialog, choose the box for “Add this data to the Data Model.”

 

 

5. A new worksheet will appear with the Pivot Table Fields list. Start to build your pivot table by dragging fields to the Rows and Columns area. When your pivot table is based on the Data Model, there will be a few subtle differences in the Pivot Table Fields list. First, the words “Active and All” allow you to add more data sets to the pivot table. Second, the name of the table appears at the top of the fields from that table.

 

6. Right-click the name of the table and choose Add Measure.

 

 

Note: The word “Measure” is a database professional’s word for Calculated Field. When the Power Pivot add-in debuted in Excel 2010, the calculated fields were called Measures. Microsoft tried to soften the word in Excel 2013, and the menu choice in Figure 1 appeared as Insert Calculated Field. This was designed to be more familiar for people using Excel. But Excel pivot tables already offer a different feature called Calculated Fields. To avoid confusion, the term changed to “Measure” in Excel 2016. I would have preferred a completely new term, such as “Super Amazing Calculated Field.”

 

7. In the Measure dialog, type a measure name such as “StatusResults.”

8. Enter the formula =CONCATENATEX(TicketData,[Status],”, “).

9. Click the Check DAX Formula button to make sure the syntax is correct.

10. You can specify the number format for Measures, which I think is great. For a text result, however, the only valid choice is General, so leave the number format as General.

 

 

Note: The syntax for CONCATENATEX is (Table Name, Expression, Delimiter). In the formula in Step 8, TicketData corresponds to the name that you used in Step 2, and [Status] is the name of the field in the source data. To use the AutoComplete feature in the Create Measure dialog, type a left square bracket. The AutoComplete list will show a list of fields from your data set. Click on one name, and press Tab.

 

11. Click OK to create the new calculated field. The calculation won’t show up in the pivot table automatically. Instead, a new field will appear in the Pivot Table Fields list. Drag the new field to the Values area.

 

The Subtotal and Grand Total results for a text field might be unexpected. The Grand Total Column of Apple and Quince will be “Apple, Quince.” The Subtotal for the Central region will be “Orange, Apple, Apple, Vanilla, Elderberry, Mango, Watermelon.” As you can imagine, the bottom-right cell containing the grand total will contain one word for every row in the original data. These results cause the pivot table columns to be unreasonably wide.

 

 

In many cases, the pivot table will look better if you remove the grand totals and subtotals:

 

12. Select Design, Grand Totals, Off For Rows and Columns.

13. Right-click on the first Subtotal label in Column A and unselect Subtotal Region.

 

The result is a pivot table with text in the Values area.

 

 

Just like any other pivot table, you can rearrange the fields in the Rows and Columns area and the results will recalculate.

 

 

Download this month’s workbook to try it out for yourself: SF-2018-07-TextValues.

 

Update from Bill Jelen: After using this method for a few weeks, I and others noticed that in some data sets, the concatenated values would contain duplicates. You can remove the duplicates by changing =CONCATENATEX(TicketData,[Status],”, “) to =CONCATENATEX(Values(TicketData[Status]),TicketData[Status],”, “). (Thanks to Rob Collie at PowerPivotPro.com for suggesting the updated formula.)

The VALUES function returns a new table with the unique values found in a column.

 

SF SAYS

 

The DAX formula language offers many new calculations for your pivot tables.


Update from Bill Jelen on January 3, 2019:

 

Comments by VB, Alex, Izabela, and JL are all pointing to the pivot table not reacting when you try to add the calculated field to the pivot table.

 

I’ve researched the problem and isolated the issue. I’ve reported the bug to Microsoft. There is a limitation in Excel that the result of a formula can’t exceed 32,767 characters. If your new Measure creates a cell that’s more than 32,767 characters, the pivot table simply refuses to react. There’s nothing Microsoft can do to increase the number of character limit, but I believe they should provide an error message that alerts you the pivot table can’t be recalculated due to the limitation.

 

There will be some data sets where the problem is the Grand Total row. If the length of all the unique values in the data set is more than 32,767 characters but the length for each customer is less than 32,767 characters, then removing the Grand Totals before adding the Measure to the Values Area will allow the pivot table to work.

 

In a similar fashion, if you have multiple fields in the Rows area, removing Subtotals for the outer row fields might allow the pivot table to work. Select Format, Subtotals, Do Not Show Subtotals.

 

The problem with all of these workarounds is that there’s no warning that the pivot table is unresponsive. That can result in a situation where you click Refresh, the pivot table won’t refresh, and, without the warning, you won’t know that it hasn’t.

 

Bill Jelen is the author of MrExcel XL: 40 Greatest Excel Tips. Send questions for future articles to IMA@MrExcel.com.
21 + Show Comments

21 comments
    Pamela May 28, 2019 AT 8:42 am

    Amazing.
    Saved me hours of time

    john May 24, 2019 AT 8:07 am

    Thank you. Most marvellous

    Bill Jelen April 27, 2019 AT 8:17 am

    Mark McColgan notes that CONCATENATEX is not working for him. If the “Check DAX Formula” button says “Check Formula”, then you are working with the Excel-2013 era bits of Office 365. The CONCATENATEX function was added to the Excel 2016-era builds of Office 365. The good news – you should be able to update your Excel from File, Account, Upgrade options.

    Mark McColgan April 25, 2019 AT 1:33 pm

    This method sounds easy, but when I try it, I always get the message “There’s a problem with your formula. Not trying to type a formula? …”. I’ve looked closely at my syntax and revised my steps several times, but can’t spot the issue. Also, in my version of Excel (365 ProPlus), I don’t get the FAX formula checker button. Could it be that this version doesn’t support DAX?

    Helmut April 10, 2019 AT 5:45 am

    Thanks a lot, amazing stuff and very helpful.

    Paul March 19, 2019 AT 11:44 am

    Hi, does anybody know how to sort/filter this text values in Pivot Table? The typical way, which can be used with numbers, doesn’t work with text…

    Ana C February 28, 2019 AT 10:52 am

    brilliant! thanks!

    Gail January 31, 2019 AT 2:31 am

    This is so useful thank you! Just a question, instead of using a comma to separate the different entries, could a new row be used (control enter) and how could I enter this?

    Miguel January 17, 2019 AT 1:02 pm

    Thank you very much Bill, this helped me and i did it without any issues.

    kt January 3, 2019 AT 12:43 pm

    I have the same problem as alex, izabela and vb, and JL. I have a sheet with three columns of text. Two could be added to Values as Measures, one could not. JL’s solution worked. Filtering to a subset of the data allows the 3rd column to be added.

    Looking at my data set, I don’t think the restriction is the number of cells with data. I think it is the total number of characters. Column one in my data has the most number of cells with data, 1936, but only 5624 characters. Column two has 154 data cells and 9394 characters. Column three has 1008 data cells and 75143 total characters. This is the one that didn’t work until I filtered it. I was able to expand the filter up to a total character count of 28591. Any additional filter items i tried to enable simply didn’t work. I checked the box, but nothing was added when I pressed “OK”. Back in the filter dialog the additional item was back to being unchecked.

    So there is limit on the number of characters than can be displayed using the concatenatex function. Something greater than 28591, but not much greater.

    Bill Jelen January 3, 2019 AT 8:07 am

    Thanks for the comments from VB, Alex, and Izabela. You’ve run into a bug or a limitation that is not documented by Microsoft. I’ve been experimenting to try to find the root cause of the issue.
    JL is on the right track. You want to simplify the results of the calculated field before trying to add it to the pivot table.
    After the pivot table is created but before adding the calculated field to the pivot table, do all of these steps:
    1. Go to Format tab, Grand Totals, Off for Rows and Columns
    2. Add all of the row and column fields to the pivot table.
    3. If you are in Compact Layout, choose the Row Labels heading and choose Format, Subtotals, Do Not Show Subtotals. If you are in the other layouts, choose one heading at a time, go to Field Settings and choose None for the Subtotals section.
    With all of the fields set to not have subtotals or totals, trying adding the calculated field to the pivot table.
    I am alerting Microsoft to this bug and seeing if they can define the exact problem.

    JL January 2, 2019 AT 5:19 am

    I have the same problem as alex, izabela and vb.
    It seems it only works with parts of the data. If i filter some data out of it, it works. If i take the full data set, it does not work.

    So try to add the field when you only have 1 record in your pivot table (using a filter). then expand the selection. I personally could not generate a correct pivottable that used more than 2000 records.
    I gave each record a unique incremental number, added this number field as the last field in my labels and collapsed it completely, then i added a filter on it from 0-2000, 2000 – 4000, etc…

    This was a solution to my problem because I can sort my data and its perfectly fine for me to cut it into pieces. I understand this may not be a solution for your problem. But maybe it helps to determine why it is not working.

    Izabela December 26, 2018 AT 4:52 pm

    I am having the same issue as VB and Alex, has anyone figured this out?

    Alex November 14, 2018 AT 11:07 am

    I am running into the same issue as VB. I am unable to drag the new measure into the values box. There is no error, I just drag and drop, the cursor hourglass spins for a fraction of a second, then nothing happens.

    Lindsey October 31, 2018 AT 10:08 am

    Is there a way to keep the values from all subtotaling without turning subtotals off completely since there is other numerical data in the rows to be summed up? I know of the conditional formatting way with custom number “;;;”, but I was hoping there was something I could apply directly to the logic instead.

    Aaron October 24, 2018 AT 11:46 am

    Thanks very much, this is fantastic. Exactly what I was looking for.

    VB October 23, 2018 AT 10:46 am

    I am unable to add the new calculated field in the ‘Values’ field of my pivot table – any ideas what the issue might be?

    harish ahuja September 29, 2018 AT 2:36 am

    thank you so much bill and special thanks to rob colliee
    who makes this pivot work.

    cb September 11, 2018 AT 5:15 am

    This works flawless. Thank you, I have been looking for this for such a long time. Eventually. super thx

    Duncan Williamson August 17, 2018 AT 8:25 pm

    Isn’t this a top tip. On my system, it is usual for the ” character to paste badly when I go from a web page to Excel or from Word or PowerPoint to excel.

    What this means is that when I copied your first concatenatex() formula, it didn’t work. until if retype “, ” at the end of the formula then it works as normal

    Anna August 12, 2018 AT 3:16 pm

    Hello Bill, Thank you for the article. I need this function very much, However, I followed all the steps, but my pivot doesn’t give add measure field. What can be the problem? Thank you and have a great day. Anna

You may also like