Learn Excel from MrExcel Podcast, Episode 2223. Show Text in a Pivot Table in the Values Area. Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question from Susan, in Melbourne, Florida. Susan wants to create a pivot table with text in the values area. She wants to report this code to have original and revised. She wants to compare those and a pivot table. I know pivot tables don't do text; pivot tables are for numbers, you know. And, at first, when she said this, I said, "What, are you just trying to show yes or no?" Because I have this, you know, weird trick when you have zeros and ones. We can create a custom number format and change the zeros and ones. The ones will be yes, negative won't happen, Zeros will be no, click OK. You can change the number format in the pivot table to change the zeros and ones to yes or no. No, Susan actually wants how to report these values. And, yeah, the answer is just No, you can't do it. But, wait. But wait, you can do it if you convert this to a real table: Ctrl+T, and then Insert, PivotTable, Add this data to the Data Model. Because that enables a brand-new formula language called DAX-- Data Analysis eXpressions. So, we'll put Market down the left hand side, and Rep, Version across the top. And instead of Code, I'm going to calculate a new measure. Alright, so we will, in fact, right-click and say Add Measure, and it's going to be called a ListOfCodes. ListOfCodes, all right. And our formula's going to use the =CONCATENATEX function. So, =CONCATENATEX now wants to know-- the first thing it wants to know is-- the table, and, of course, I just press Ctrl+T, which means that that's going to be called Table1. So I start to type Table1 here, and we choose from the list, and My expression is going to be the Code. So I start to type Code, Table1[Code] and then the delimiter, what we want between each one, I'm going to put in-quotes, comma, space-- ", " -- like that, close-- )-- Check DAX Formula-- Formula has no errors. Leave the category as General; we don't want to report this as Number. Click Ok, and then what we get is this ListOfCodes is now here, and I can take that and drag it to the Values area. Now, check this out-- this is actually, actually working. So, for each item, we're seeing what it was. So, in Atlanta, Gary had Fig. Mike had Cherry, and then in the Revised it's Fig and Orange, and then the Atlanta Total is reporting all of the values, separated by a comma-- so Fig and Cherry. And, if we take Market out and put Region in-- let's take Rep out-- so, now I'm seeing all of the items that were reported in that region. Alright, and then down here in the Grand Total, all of the items again. This is pretty darn cool using the Grand Total. Now, one of my pivot table defaults is, Subtotals, Include Filtered Items in Totals, and it looks like that is not having an impact. Over here on the far right-hand side we have a Grand Total there, so down at the bottom, we're essentially getting a list of everything. This might be a pivot table where you really don't need these grand totals. Remove Grand Total, and maybe even you don't need these grand totals-- right-click and Remove Grand Total. Very cool way using the CONCATENATEX function-- the CONCATENATEX funtion. Let's take a look at that again: So, =CONCATENATEX, the name of the table, the column, and then the delimiter is in ", " It's always awesome to see what new things we can get just from choosing that box. Add this data to the data model. That box is discussed in my book, "MrExcel LIVe, The 54 Greatest Tips of All Time," along with a lot of other tips. Click that "I" on the top right-hand corner to check it out. All right, wrap up for today. Susan in Melbourne wants to create a pivot table that shows text in the values area. Typically, yeah, no. No deal. But it is possible with the DAX formula language. In order to use DAX, you have to format your data as a table using Ctrl+T, Insert, PivotTable, and then check the box for Add this data to the Data Model. If you don't do this, it's not going to work. Build the row and column areas of the pivot table, right-click the table name in the fields list and choose Add Measure, and then the formula is, =CONCATENATEX. Very much like the new TEXTJOIN function. I specify the table, which field, and then the delimiter, and it works. To download the workbook from today's video, visit the URL in the YouTube description. Thanks to Susan for coming to my seminar and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
