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
amitchandak's avatar
amitchandak
Super User
6 years ago

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.


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

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      6 years ago

      MKushpil ,

      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?

      • MKushpil's avatar
        MKushpil
        Regular Visitor
        6 years ago

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

         

         

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      In For quite many rows, the numbers in column H match the numbers in column C.  Where they do not, the differences are very small.  So my simple lay person question is that why do you need the complicated SUMIFS() function when in face you are getting the same numbers which are already present in column C.

      • MKushpil's avatar
        MKushpil
        Regular Visitor
        6 years ago

        Dear Ashish_Mathur,

        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.
        Let's go a little further to H7.

         

        I decide that the purchase from 04/03/2019 160 pieces should have an account assignment not T.01.03.01, but T.01.03.02 (it's error).

        It means for H7 calculation that we no longer have additional 4225828,46 in the numerator and additional 160 pieces in the denominator.

         

        So, calculation for H7 was:
        ( 2431082,5 - 799260 + 4225828,46 ) / ( 146 - 48 + 160 ) * - 1 = - 22704,07 - before changing WBS Element

        And then

        ( 2431082,5 - 799260 ) / ( 146 - 48) * - 1 = - 16651,25 - after changing WBS Element

        My task to understand this delta (before and after WBS changing). But It's possible for me only by, as you said, the complicated SUMIFS() function with dynamic range.