However, I’ve been asked to restructure the table so that:
Values appear first (as the primary grouping)
Followed by the split into Regions and ST
In other words, I want the metrics (values) to be displayed as the top-level grouping, with Regions and ST nested under them—similar to how some tools allow “Values as rows” to be prioritized before row dimensions.
Question:
Is it possible in QuickSight to:
Reorder the pivot table so Values come before Rows, or
Achieve a similar layout via calculated fields, field wells configuration, or any workaround?
If not directly supported, what’s the best alternative approach to mimic this structure?
I think I came up with two potential solutions for this. The first option is not an exact solution, but I just want to show you a native restructuring option first. Keeping your Region and ST in rows and metrics in values, you can control the value position in the properties panel. Refer to the screenshot below:
Like I previously said, this is not an exact solution since it does not have the metric values first but was just something I wanted to showcase that is specific to pivot tables. The next option requires creating a parameter and two calculated fields to handle the metric-based grouping. You are first going to create a single value string parameter and connect it to a control that lists out all of your metric options. Then from there you are going to create a ‘Metric’ calculated field that will look something like this:
This calculated will hold the grouping logic and will be placed in rows right above your region field. Next, you will need to create a calculated field to hold your metric values and that will look something like this:
This Metric Value field will then be placed in Values for your pivot table. Once all the parameters and calculated fields are created, your visual should look something like this:
I would argue this is the best approach, but I do want to state the one caveat with this approach is you can only show one metric at a time and can switch between metrics via a control. You can potentially create the grouping logic to get all of your metrics included in the visual but that will have to be through restructuring your dataset and essentially creating a metric field on the dataset level. Moreover, I would hesitate to do this approach as it may result in significantly increasing the size of your dataset to account for the metric grouping.
Hi, @richa.jha . We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution" (check box under the reply)
