I’m trying to create a new dataset in Amazon QuickSight by grouping data from an existing dataset. For example, I want to aggregate values (like total revenue or count of orders) by fields such as customer_id or product_category. My goal is to use this grouped/aggregated data later, either for joins or in visual analysis.
However, when I try to apply grouping or aggregation in the data preparation phase:
The changes apply only within visuals, not at the dataset level.
I cannot save this grouped result as a separate dataset.
Custom aggregations and calculated fields don’t reduce the granularity or allow me to persist a grouped version.
What I’m Looking For:
A way to group and aggregate records during dataset preparation (e.g., using GROUP BY logic).
A method to save the result of this grouping as a new dataset that I can reference later or join with other datasets.
I am looking from some other way than custom sql .
Hi @ssarv309 and welcome to the QuickSight community!
You are correct in that you are encountering limitations within QuickSight as the dataset layer is not specifically setup to handle this level of prep.
Additionally, using some type of aggregated setup would be possible, but would need to be handled with custom SQL. If you did that, you could aggregate some of your fields using parameters, but like you said, it will only affect the visuals where that dataset is connected, not the actual dataset.
To handle this type of dataprep, you may want to explore other AWS services that are setup to handle these actions more effiiciently, like Glue.
One other work around you could explore for taking aggregated data and utilizing as a new dataset..you could setup a table visual in an analysis, perform your aggregations on the table and then export to csv or excel. Then you’ll have that aggregated data setup and ready to be reused.