# SUMIF with PowerPivot



## kuba1989 (Jul 16, 2014)

Dear All,


I have a problem. I need to use SUMIF'ish function in PowerPivot that would go something like this.


A 1
B 2
C 3
A 10
A 50
B 60
B 10

And for this Data I would like to get

A 61
B 72
C 3
A 61
A 61
B 72
B 72

So it would be a sumif with changing criteria for each row. How to solve this? Thanks guys !


----------



## XLBob (Jul 16, 2014)

The first formula will give better performance if you have a big data set

1. CALCULATE(SUM([Sales]),ALLEXCEPT(Table1,Table1[item]))

 2. SUMX(FILTER(Table1,Table1[item]=EARLIER(Table1[item])),[Sales])


----------



## lokesh221188 (Jul 18, 2019)

XLBob said:


> The first formula will give better performance if you have a big data set
> 
> 1. CALCULATE(SUM([Sales]),ALLEXCEPT(Table1,Table1[item]))
> 
> 2. SUMX(FILTER(Table1,Table1[item]=EARLIER(Table1[item])),[Sales])



Dear Bob,

Could you help me with the below ?

In my case i would like to calculate cumulative totals for data containing GL Account Balances (Column 1) for each of the Financial period (Column 2) having the "Current period activity (Column 3).

I would like to perform the below

Account | Period | Amount | Cumulative
  A100          1           100              100
  A100          2           200              300

So i want to calculate the cumulative column in Power Pivot, also would be helpful if the same could be done using Power Query.

Thanks in advance.


----------

