Hello all,
I want to create a running total by product category in power query; so far I've come up with the following;
<VAR prod = TESTA[Product]
RETURN CALCULATE(SUM(TESTA[Qty]),FILTER(TESTA,TESTA[Index]<= EARLIER(TESTA[Index]) && TESTA[Product] = prod))>
variable name pod , table name TESTA , Category Product; I have two questions; I have been trying to use a similar formula by using SUMX but cannot get it to work I've tried;
<VAR prod =TESTA[Product]
RETURN CALCULATE(SUMX(FILTER(TESTA,TESTA[Index]<= EARLIER(TESTA[Index] ) && TESTA[Product]= prod ),TESTA[Qty]) )>
but this does not work.
My second question is that I would like to use a second variable rather than EARLIER function by again am getting it wrong.
Any help welcome.
Richard
I want to create a running total by product category in power query; so far I've come up with the following;
<VAR prod = TESTA[Product]
RETURN CALCULATE(SUM(TESTA[Qty]),FILTER(TESTA,TESTA[Index]<= EARLIER(TESTA[Index]) && TESTA[Product] = prod))>
variable name pod , table name TESTA , Category Product; I have two questions; I have been trying to use a similar formula by using SUMX but cannot get it to work I've tried;
<VAR prod =TESTA[Product]
RETURN CALCULATE(SUMX(FILTER(TESTA,TESTA[Index]<= EARLIER(TESTA[Index] ) && TESTA[Product]= prod ),TESTA[Qty]) )>
but this does not work.
My second question is that I would like to use a second variable rather than EARLIER function by again am getting it wrong.
Any help welcome.
Richard