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:

image

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
    image

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?