CALCULATE and SUM

[vbaisdaxing]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Letters[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Hello Everyone,

What i am trying to accomplish is to run a CALCULATE function in conjunction with sum to always sum up the totals of its specific letter. In addition, I would like a= 25, b=5, and
c=30 even after slicing the data.

Any suggestions will be greatly appreciated.

 

[jarjarbingie]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Letters[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Hello Everyone,

What i am trying to accomplish is to run a CALCULATE function in conjunction with sum to always sum up the totals of its specific letter. In addition, I would like a= 25, b=5, and
c=30 even after slicing the data.

Any suggestions will be greatly appreciated.

I would use either a pivot table or SUMIF function:

=SUMIF($A$2:$A$10,E2,$B$2:$B$10) where E2=a

but it depends on the "slicing" you would be doing

 

[Matt Allington]

I would like a= 25, b=5, and
c=30 even after slicing the data.

It is impossible to answer this question accurately without knowing the table names, relationships and what you are wanting to slice on. However, assuming you have 1 table, and assuming you have letters in a pivot table, the following will always give you totals by letter regardless of any other filter coming from the table.

total =CALCULATE(sum(table[value]),allexcept(table,table[letters]))

 

[vbaisdaxing]

greatly appreciated. that's what I exactly needed. Thank you.