Pivot table calculate net value

Following is my data:
Symbol, Fund, Group, Amount

The Amount field can be positive or negative
I want to always net the Amount by Symbol, regardless of the grouping in the pivot table.

Can you suggest me the formula for the same?
Thanks.

Hi @harpar1808 and welcome back to the Quick Community!

Since Amount already carries the sign, the net is just the sum of Amount at the Symbol level. A level-aware aggregate (LAC-A) locks the calculation to Symbol, so Fund and Group in the pivot don’t split it up:

sum({Amount}, [{Symbol}])

One thing to note, pivot tables do not support row or column totals for LAC-A fields yet.Meaning that if you turn totals on, they may show blank for this field. If you’d rather use a window function, sumOver({Amount}, [{Symbol}], PRE_AGG) returns the same net on every row. Just leave its aggregation on the default (MIN) so the repeated values don’t get added together. The level-aware calculations docs cover both. If neither fits your layout, let me know how Symbol, Fund and Group are placed in your rows and columns.

Actually, maybe I was not correct in my explanation. Here is the data that I have:

As you can see, I group by fund → position_group → symbol.

Gross USD is a calculated column with calculation → sum(ifelse(side=‘Long’,{hcur_gross_exp},{hcur_gross_exp}*-1), [symbol])

Problem: the Gross USD is showing a value even though the raw values in database is 0.

What I want is that the “Gross USD” should only sum by symbol for all the rows belonging to that Fund → position group hierarchy.

Hi @harpar1808,

Thanks for the screenshot, that makes it much clearer. The issue is the [symbol] in your formula. It makes the field a level-aware aggregate (LAC-A) that groups only by symbol and ignores fund and position_group, so every AAMI row shows AAMI’s net across all funds. That’s why you’re seeing a value under WPF0-AD even though hcur_gross_exp is 0 for AAMI there.

You don’t actually need LAC here, since the pivot is already grouping by fund, position_group and symbol. Create a row-level calculated field like ifelse({side} = ‘Long’, {hcur_gross_exp}, -1 * {hcur_gross_exp}) and add it to Values with Sum aggregation. Each cell will net the Long and Short amounts for that symbol within its fund and position_group, and your subtotals and grand total will work too. If you’d rather keep it as LAC, put the full hierarchy in the brackets: sum(ifelse({side} = ‘Long’, {hcur_gross_exp}, -1 * {hcur_gross_exp}), [{fund}, {position_group}, {symbol}]). That gives the same row values, but LAC-A fields show blank totals in pivot tables, so I’d go with the first option.

Also, check that the quotes around ‘Long’ are straight quotes in the calculation editor, since the ones in your post look curly (that could just be the forum). The level-aware calculations page covers the grouping and totals behavior in more detail: Using level-aware calculations in Quick Sight - AWS Quick , and the ifelse syntax is here: Ifelse - AWS Quick