hello everyone, do you know how I can sort the values of a pivot table with data in the value field? in the row field box I have no data, neither in the column field, but with data only in the value field, how can I sort them in ascending or descending order?
Can you explain why you need Pivot for this particular visual? At the moment you can’t do it in pivot with just values but you can with table visual. Change the visual type to Table and in the field wells pick the field in values list you would like to sort on, click on drop down arrow to choose the sort option.
1 Like
If I understand your use case - You have a list of KPIs/measures and you would like to show them in a single visual ordered by the the values of these KPIs. Am I correct?
Assuming you don’t learn any better solution (I am new to QuickSight) - I would create a separate dataset with the table of KPIs and values and include them in the Analysis. The schema of the table can be: KPI Name | Value. If you want to filter the KPIs by some dimensions, you would need to add them into that dataset.
if you click the value column in the pivot table visual, you will see a sort option
more details you can find in the QS user guide: Sorting Pivot Tables in Amazon QuickSight - Amazon QuickSight
Hi jjc,
I don’t think the sorting option is available when you switch the Pivot to show Values as Row.
Hi Gil, How did you switch the pivot to show values as row? Do you mean drag a column from the values field to groupby field? Best,JJ
@jjc -
In the Values field well of the Pivot visual, you can toggle between Row and Column as shown here:
Hi Gil, I think it makes sense to me the sort option is not available when toggling to rows under the values, as the values from 2 different columns of a dataset now is in one column of the pivot visual. Could you clarify a little more on any use cases of doing sorting of a pivot table when values are as rows?
@jjc,
The main use-case is when you have a dimension with small number of unique values and high number of measures to display that will not fit horizontally in your report (without scrolling to the right, which isn’t user-friendly). By moving the dimension field to the Columns field well, and moving all measures to Values, you can now show a vertical-oriented Pivot that will better fit your design purposes.
For example: Imagine you have a Color column with Red, Yellow, Blue, and 10 different measures for those. Now using the Rows toggle you can make your Pivot to be a 10x3 matrix instead of 3x10.
This is even more relevant if you want to show a long list of different KPIs without any dimension. The reader will prefer reading the KPIs as a vertical list, like bullets in a presentation, rather than vertically.