Excel: Text in the Values of a Pivot Table

When you create a pivot table, it only shows a summary of the numerical values. However a lot of the time a Pivot Table is meant to summarise the actual text of a table.

Because of this limitation not many people end up using PivotTables.

There's a way of getting the text to display instead of the numerical values - but it's tricky!

For instance, say if you would like your Pivot Table to collate IDs that match a certain heading and status like this:

Up until recently this was not possible, since the Values area in a Pivot Table only allows a numerical calculation, such as Sum or Average or Count.

However Excel has a new feature called DAX formulas, that allows simple text operations to appear in the Values area. This means that you don't need to delve into Power Query, or perform complex database queries, or write some horrible word or macro involving arrays, to get the report output that you need.

Below is an example on how to create the Controls field:

Convert the data region into a table:

2. Insert a Pivot Table, making sure you add the table to the data model:

3. Add a new "Measure" which will be a DAX formula that allows text:

4a. If plain text, enter the DAX formula:

for unique values:

=concatenatex(distinct(Table1[Identifier]),Table1[Identifier],", ")

or if you want all values (note this may fail due to the length!)

=concatenatex(Table1,Table1[Identifier],", ")

where Identifier is the field name of the identifier.

4b. If numbers, enter the DAX formula:

=concatenatex(values(Table1[Identifier]),format(Table1[Identifier],"0000"),", ")

where Identifier is the field name of the identifier. Note that the above example formula will format the identifier into four characters, and add a comma between each value.

Handy hint: For the delimiter, you can also use "SHIFT-ENTER" to separate entries using a new line (enable "Wrap Text" for the column).

5. Drag the new parameter into the Values area:

Below are links to the web page that show how to do it:

https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/

https://www.mrexcel.com/excel-tips/excel-2020-text-in-the-values-of-a-pivot-table/

Group By Alternative

In O365 there is the groupby and pivotby functions that will allow you to do the same thing without pivot tables and DAX formulas: https://techcommunity.microsoft.com/t5/excel-blog/new-aggregation-functions-groupby-and-pivotby/ba-p/3965765

You'll need to use a LAMBDA function to achieve this. However this has the advantage that groupby computes all changes immediately and you don't need to refresh the pivot table.

The general formula for comma-separated values is:

=GROUPBY(tbl[Category],tbl[Item],LAMBDA(x,ARRAYTOTEXT(SORT(UNIQUE(x)))))

A similar formula can be created with TEXTJOIN to use CHR(10) carriage return (line breaks) between each value.

Page updated

Google Sites

Report abuse