Forum Discussion
Dynamic SUMIFS function into calculated column by DAX
Dear amitchandak,
thank you for your feedback! Sure, file attached.
My task is simulate price for 221 and 231 Movement Type [ Column E] in Value TranCurr Simulation column [ Column H].
I'll try to explane on H5 example.
If Movement type is positive then take Value TranCurr else
SUMIFS (Sum range = $H$1:H4 = all previous rows; Material range = $A$1:A4; Material = A5 ... )
In result we have calculation: ( ( 2431082,5 - 799260 ) / (146 - 48) * - 48 ) = -799260 (H5).
The main problem is my SUMIFS function has dynamic range in my calculated column (all previous rows). And on every second row my range gets bigger. I have 100k rows and my Excel cannot make this function. In powerpivot I also don't know, how to make it by DAX, because there I have dependecy error (link on calculated column).
Resident RockstarMKushpil ,
In all cases in your sample data the Value TranCurr Simulation is the same as the Value Tran, are you sure the formula provides the values you want. If not is there are different way to generate the simulation withour having a recursive sumif of the same column?
Dear richbenmintz ,
As I already answered, you are right, in my current example I have the same figuers in Column C and my simulation Column H. I did this to test the behavior of the transactions when calculating the weighted average. How I understand, I cannot avoid this recursive sumif of the same column. If somebody have any ideas, I'll be happy!)
- richbenmintz
Hi MKushpil,
I guess what i am trying to understand is, are you able to derive the simulation without summing up the sums in colum H. The dynamic range is not a problem the self referencing sum is the problem