Inherited Custom Calculated Field in Direct Query not recognizing aggregation in Pivot Table Visual

Hi All,

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:


Field in parent dataset:

Field in child dataset:

Any ideas or workarounds?

I’ve tried to create an example in my end.

  1. Add a calculated field to show total sales
    TotalSales: sumOver(sum({Sales}),[])
  2. Add a child dataset and use direct query
  3. 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.

Thanks for letting us know your workaround @sshs4515.