Forum Discussion
Dynamic SUMIFS function into calculated column by DAX
Dear friends, hello!
My task is to create a 100,000 line pricing simulation. Unfortunately, excel cannot handle this amount. I tried to do it through DAX but I lack knowledge. Is it possible to make an analogue of such a formula?
I need to creat dynamic sumifs function in "calculated" column. Here is a fairly simple algorithm, but due to its massiveness, I cannot apply it in excel. I will be very grateful for help.
11 Replies
MKushpil , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format?
Appreciate your Kudos.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).
Hi MKushpil,
Thanks for providing the sample data and excel doc. let me see if i can create a formula for you.
