Question about difference calculation in pivot table

Question & Answer
,
1

Hi,

I have a Analysis as such:

Pasted Graphic 32 copy
Pasted Graphic 32 copy1045×169 19.2 KB

With the same aggregation/dimensions showing in table, is there any way I can calculate difference between A-B, A-C, A-D and so forth?

From difference - Amazon QuickSight it says this function only works for the difference between measures based on one set of partitions and sorts. Therefore, if index is -1, right now I can only achieve A-B, B-C, C-D and so forth.

Thank you for any help!

2

You can compute A-B, A-C or A-D by defining few calculated columns.
ValueIfA: ifelse({Dimension}=‘A’, {Measure}, NULL)
ValueIfB: ifelse({Dimension}=‘B’, {Measure}, NULL)
Difference: sumOver(sum(ValueIfA), [Country])-sumOver(sum(ValueIfB), [Country])

(this is if measure is aggregated using sum function)

2 Likes
3

Thank you Tatyana! It works like a charm!