Search code examples
amazon-web-servicespivot-tableamazon-quicksight

Calculated field Sub total in pivot table is not displaying correct value

I am working on QuickSight in AWS. I am trying to achieve weighted average value in a Pivot table.

  • I am using SPICE data to create this analysis.
  • I have created a calculate field (WAM) in analysis with formula "percentOfTotal(sum(upb),[{pool_num}]) * sum({remaining_terms})".
  • This gives me the desired value on each row level, but the sub total of a particular column is not reflecting the total of values in the calculate field, rather it displays the sum of original values in the "remaining_terms" field.

Please see below image for the same. Can some one please through some light on this ?

enter image description here

Thanks in advance for your help

Please note that I have tried the same in Excel pivot table and it works perfectly.

Solution

  • I have solved the problem in a different way. See below what I have done.

    WAM = percentOfTotal(sum(upb),[{pool_num}]) * sum({remaining_terms}).

    It looks like QuickSight treats the subtotal as a row and the above function is applied on the subtotal, hence it is converted as

    (1186272.5 / 1186272.5) * 31 = 31.

    I have tried to produce the desired result by introducing another custom field with formula

    SUM_WAM = sumOver({WAM},[{pool_num}]).

    This gives me the output I need, but in a column. See the screen shot attached enter image description here