Forum Discussion

MKushpil's avatar
MKushpil
Regular Visitor
6 years ago

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...
Need Help
MKushpil's avatar
MKushpil
Regular Visitor
6 years ago

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).

 

https://yadi.sk/i/TkOMfHXVq2R0ZA

richbenmintz's avatar
richbenmintz
Resident Rockstar
6 years ago

Hi MKushpil, 

 

Thanks for providing the sample data and excel doc. let me see if i can create a formula for you.