I got a rather interesting question from someone who attended one of my pivot table webinars. They were wondering if they could have text values in the Values area of a pivot table?
This is usually the area where we summarize fields by various different aggregation methods like taking the sum, average, minimum, maximum or standard deviation.
But the thing is, these aggregation methods require numeric data!
Is there any way to summarize text based data that will return text as the result?
The answer is yes, but we will need to use the data model and DAX formulas to do this. Traditional pivot tables do not have this functionality.
Also, we will need to be a PC user with Excel 2013 (or later) and Office 365. Sorry, but these modern features aren’t available in the Mac versions yet.
Here we’ve got a list of students along with the courses they are enrolled in. A student can have multiple rows of data when they are enrolled in multiple courses.
Can we summarize this data with a pivot table so that we just display each student once and then show a comma separated list of their courses?
Insert A Pivot Table
First, we will need to insert a pivot table. This is done in the usual manner.
Select a cell inside the data ➜ go to the Insert tab ➜ then press the Pivot Table button.
In order to use DAX formulas, we will need to select the Add this to the Data Model option.
Add A Measure
With traditional pivot tables, we don’t need to define any calculations. They come predefined with basic sum, count, average, minimum, maximum, standard deviation and variance calculations.
With the data model, we get access to a whole new world of possible calculations using DAX formulas. These are created by adding Measures. We can create just about any calculation we can imagine with these.
To add a Measure, select the pivot table ➜ right click on the table of data found in the PivotTable Fields window ➜ choose Add Measure from the menu.
This will open the Measure dialog box where we can create our DAX formulas.
In the Measure dialog box:
- We need to select the table to which to attach our measure, give the measure a name and description.
- We can explore the available DAX formulas using the Insert Function menu and also check the validity of any formula we write.
- We can write our formula in the DAX formula editor.
- We can assign formatting to the measure.
To create a measure that aggregates text into a comma separated list, we’re going to use the ConcatenateX DAX function.
We need to write the above formula into the DAX formula editor and then we can create the new measure by pressing the OK button.
This will take the Course field from the StudentData table and concatenate its values together with a comma and space character as a delimiter.
Using Our New Measure
This new measure will appear listed in the PivotTable Fields window with all the other fields and we can use this new measure just like any of the other fields.
Measures can easily be identified from the data fields by the fx icon in from of the measure name.
We can click and drag the Course List measure into the Values area of our pivot table and this will produce a comma separated list of a students courses.
Pivot tables have been a feature in Excel for a long time and they can do a lot of great useful calculations.
They are limited though, especially when it comes to displaying text values in the Values area.
With the data model we get many new calculation options that regular pivot tables just don’t have, including concatenating text values to display in the Values area. This is definitely a feature worth exploring when regular pivot tables just won’t cut it.
This was very helpful! I’m having some trouble adding my new measure field to the Values area. For some reason, it is not populating. I am trying to do this for a very large table of data (tens of thousands of rows). Is it not able to process it when using it with a very large source of data? Other measures (that reference different fields) work okay. I tried this particular measure on a smaller sample of 100 rows of the data, and that seems to work. Any idea what the problem is and how to fix it?
Yes, the X DAX functions iterate over the entire dataset, so are resource intensive.
Also, a cell has a limit of 32k characters, so results might be truncated.
Similar to previous comments, I could only add the Measure to the pivottable Values section if the source table was around 3,000 rows max. In my case, the source data was 32,000 rows. So, to get close to the results I was looking for, I split the source data into new standard tables that were 3,000 rows max, and then created individual Pivottables from the individual standard Tables. Then I manually pasted the individual “PivotTables” into one standard table.
The 32K character was my issue. But it was because of the Subtotal and Grand Totals cells were probably exceeding the limits. So, I simply cut off Subtotals and Grandtotals in my PivotTable, and now it works. I learned it from: https://sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table/
Yep, only 32k characters per cell.
Am loving this new discovery and it’s added a whole new level of productivity to my day to day… I use this with a main table of countries, and the various configuration options that the client has selected. Where the client has multiple entities for a country, I might have several rows for the same country, but then the results in the pivot are naturally duplicated as expected. This is fine where there are different options, but when each entity for the country is being set up the same, is there a way to eliminate these duplicates? eg:
Instead of ” Germany | SSO, SSO, SSO, SSO | Indirect, Indirect, Indirect, Indirect,”
I’d like to see ” Germany | SSO | Indirect ”
Am I hoping for too much?
You can try something like this to get only unique items:
= CONCATENATEX ( VALUES ( StudentData[Course] ), StudentData[Course], ", ")
The VALUES function returns the unique items from a column.
OMG OMG OMG OMG OMG! I’ve been tinkering with this for weeks and… OMG… IT WORKS! Thank you so much.. I can’t thank you enough! THANK YOU!
Can we give a line break? by using any delimiter? Let just say we are trying to separate cells text by adding a line break.
Yes, char(10) as the delimiter will do the trick.
While using the ConcatenateX Function, how do I stop Excel from calculating text cells with a Pivot Table result. I only want to calculate the numeric fields.
I don’t think you can. If they’re in the same column the data will all be text type. Data types can only be assigned to a column, not individual rows in a column.