calculating the sum of a column in the range specified in another table's row

[derk_l]

Hello,


A question about powerpivot and excel 2010.
The entire thing is pretty big and complex,
so I use an example to explain the problem.
(please do not explain how this particular
example could be done in another way, the
real thing is different from this example!)


I have two tables with a different number of rows.


Table1:
column1 column2
1 1
2 2
3 4
4 8
5 16
6 32
7 64
8 128




Table2:
column3 column4 column5
1 8 (should be 255)
2 8 (should be 254)
3 8 (should be 253)
1 1 (should be 1)




I want to calculate the sum of column2 with a range,
column3 to column 4, and put that sum into column5


An example formula that works only works for the second row:
column5:
=calculate( sum([column2]) ; [column1]>=2 ; [column1]<=8 )


What I really want is to replace the 2 and 8 with the values
of column3 and column4, but doing that results in an error.


Which formula can do the trick?


Thank you in advance for your replies!!!


Derk

 

[Midnight412]

I think you might be able to do this with the INDIRECT function.

 

[derk_l]

Isn't INDIRECT an excel function? I cannot find it in powerpivot.

 

[derk_l]

It looks like I found the problem! You have to filter, the filter of calculate allows only a combination of a field and a constant, instead add: filter( [column1]<=[column4] ) and the same for column3.