0

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.

JD D
  • 7,398
  • 2
  • 34
  • 53
Raju Rudru
  • 1,102
  • 12
  • 19

2 Answers2

1

Try to remove the 2nd argument from the percentOfTotal function. For example, just do:

percentOfTotal(sum(upb))

I am not 100% this will work but one thought it that it would match the remaining_terms value if the percentageOfTotal was 1 (i.e. 100%) and you may not need to provide a partition argument in a pivot table since pivot tables implicitly provide partitions.

JD D
  • 7,398
  • 2
  • 34
  • 53
  • Thank you JD D, for your response. I have tried the way you have told. This works in Excel Pivot table, but not in AWS QuickSight. Could this be an issue in QuickSight ? – Raju Rudru May 27 '19 at 05:52
0

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

Raju Rudru
  • 1,102
  • 12
  • 19