Custom Calculation in Pivot table

DNK

Code	sum	alternate Sum
xyz	5	14(Sum (abc+pqr))
abc	8	11(Sum(xyz+pqr)
pqr	6	13(Sum(xyz+abc))

Hello Friends,

I have a pivot table like above. Now I need to calculate the Alternate Sum for the above table in the following way

for the Code xyz Alternate Sum = Sum(abc+pqr+.....)

for the code abc Alternate Sum=Sum(xyz+pqr+.....)

for the code pqr Alternate Sum= Sum(xyz + abc+.....)

There may be additional Codes as well in the Code field.

Thanks for your help.

DNK

Gianni Ceresa

Hi,

Isn't it a simple sum at the total code level minus the actual value if sum ?

So code is part of an hierarchy, you use aggregate at the parent level and then subtract the value for the current row.

DNK

Thank You .This is not straight forward.Actually I am doing a Sum after the Count of some condition on that row is happening.

Something like Sum(Count(Case when Condition)) once I get the total sum, I can filter that for that row by other rows.

This is more like a transactional report  so no Hierarchies are properly designed.

Thanks

DNK

asim cholas

Dear DNK,

Try the below formula,

sum("YourAmountColumn"by 'None') - sum("YourAmountColumn")

Thanks

DNK

Thank you Asim,

I figured myself last night!

BTW, Can you help me how to close this discussion?

DNK

asim cholas

Dear DNk,

Post the resolution and make it as answered(correct answer) as u figured it out. It would be helpful for the people who are referring to the forum later.

Thanks

DNK

I did the samething as you mentioned earlier!