How to Display text field (no calcualtion) to a pivot table as value?

I have a pivot table and want to see a text field as pivot values. Can I turn off the aggregation to just present the text as it is?

1 Like

Can you tell us more about values in that text field?
What would you like to see if given the dimensions that are placed into Rows or Columns, this field has multiple values?

At the moment, if you want to see unaggregated text fields in a pivot table, they need to be added as Rows or Columns. If you don’t want to do this, can you explain why?

Hi Tatyana, this text field (under values box of the field wells) will not have multiple values as I will make sure the fields I dragged into the rows box of the field wells will produce unique value of that text field.

Please see the workaround in excel for proof of concept. More details of instructions can be found: Pivot Table With Text in Values Area - Excel Tips - MrExcel Publishing

QuickSight does not provide an aggregation that can return value as is for text measures. The only available aggregation methods at the moment are Count and Count Distinct.

We will likely provide other aggregation methods in the future (there is one that is used to display text in tooltips which is not exposed as an aggregation to be used in a pivot table yet) however we don’t have a date for that at the moment.

I will share this thread with the program manager for the area so that we can consider prioritizing this work higher.

3 Likes

is there any updates on this

2 Likes

@jjc
I also have the same requirement but there’s still no solution for now. I believe the solution would be to allow min and max on text fields (the same way that min and max work on string columns in SQL).

4 Likes

@Tatyana_Yakushev Any update here? This seems fairly straight forward and is blocking us on a couple of projects. Happy to provide any evidence or information to make it higher priority.

1 Like

Hi @ajColaizzi,

In some cases you can use the lastValue or firstValue function as workaround but all the fields that are in your calculated field have to be added to the visual (and then hidden if you don’t want them).

2 Likes

Any update on this? Has this feature been enabled?

3 Likes

Any update on this? The lastValue won’t work because it requires the column calculated to be part of the rows/columns. I need to find a way to calculate the max (or distinct) text string to add to a pivot value. Any updates?

3 Likes

@Tatyana_Yakushev Would also appreciate any updates on the following , is it even in development ?

2 Likes

HI
I need to display couple of fields that are text fields in the pivot table. I know that the only way to show them is to switch them to the rows from values. But this is a report and the sequence of fields in the visual must be adhered. If I move those text fields to rows section, it becomes very illogical to show those right next to line items.
Really looking for a resolution for this.

@David_Wong Could you elaborate on this workaround? In which cases can one use firstValue and how should the calculated field be defined. I’m having problems even creating a calculated field where the first argument of firstValue is a string.

@GdooM

It depends on your dataset. For example, if I want to use a pivot table to show a user’s answer to a question, I can write a calculated field like this:
Answer (Last Value) = lastValue(Answer, [{Attempt ID} ASC], [Question])

In this example, I would need to also add Answer and Attempt ID to my pivot table to avoid any errors.

If your calculated field is giving you an error, can you show your calculated field and the error that you’re receiving?

1 Like