Any idea why a QuickSight visual is not recognizing a calculated field as an aggregate when it is pulled into a direct query dataset?
The calculation sits in a parent dataset; I have a direct query that pulls these calculated fields into a new dataset. But when I try to add to a Pivot Table visual, it treats the field like a typical number and tries to apply a “SUM” aggregation in the pivot table, and creates this error:
Add a calculated field to show total sales
TotalSales: sumOver(sum({Sales}),[])
Add a child dataset and use direct query
Create a pivot table and it was succeed
Can share the purpose for adding the new calculated field? did it show the same error if you are using the parent dataset to create a pivot table? what’s the details of the error?
No, the field does not show the same error when using the parent dataset. This error happens for any calculated field that I use via direct query from the underlying dataset.
As a workaround, I duplicated the field calculation in the child dataset. This isn’t the best from a maintenance perspective as if the formula changes I have to modify in both the parent and child data sets (parent is used for other reports), but it did resolve my issue.
