Pivot: IRR calculation at different levels (SAA bucket and Groups)

Q&A
,
1

I have a Quick Sight pivot in which rows are nested like this:
SAA bucket
└─ Group

For each year-end month (example: 2020-12) I need to show an IRR, but the source field differs by row level:

  • Bucket rows should display the field xirr_s_ytd_usd
  • Group rows should display the field xirr_g_ytd_usd

What I already tried:

  • ifelse(isNull({Group}) OR {Group} = ‘’, {xirr_s_ytd_usd}, {xirr_g_ytd_usd})

Even with aggregation set to Default, Quick Sight still adds the IRRs on the SAA-bucket subtotal, which is meaningless. More complex attempts that wrap each branch in sumIf or use level-aware functions fail with “custom aggregations cannot contain both aggregated and non-aggregated fields" or “We encountered an error trying to save you calculated field. Please try creating it again”.

Question:

  • How can I build one calculated field that shows the correct IRR in every row (switching by level) and does not sum or average the IRR on the bucket subtotal?
2

Hey @dn17

I’m not sure that this is going work because the column is always going to create a total. I think your best option is to have a column per metric or change how you have organized your pivot table.