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).
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.
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 ElementAnd 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.
Not sure i understand your logic, the purchase from 04/03/2019 has a positive Total Quantity, which bypasses the SUMIF logic, value of H is 4,225,828.46. The SUMIF only deals with negative Total Quantities. How do you determine that the values are assigned to the incorrect account and if we are doing an account reassignment, could we not just update the WBS Element? then do a straight sum using the reassigned WMS Element to group by.
Sorry if I am being dense.
Richard
Dear richbenmintz,
My example shows the fact that there is in SAP. But for 4,225,828.46 account is worng (It's my personal de facto decision, not formula result). This purchase in euro, that's why I need change account and then in my second transaction I will have negative effect, because my fact weighted average bigger than should be. And every previous buy/write off transaction is in formula, therefore, I don't know how to avoid it.(
