topic Re: Calculate number of top rows with sum of their values equal to a particular dynamic value in DAX Commands and Tips

Calculate number of top rows with sum of their values equal to a particular dynamic value

manuvats — Sun, 29 Aug 2021 15:29:12 GMT

I have a table of Sales Order Details which has a Sales Order Number Column, Item Code Column and Quantity Ordered column. Let us call this SalesOrders

OrderNo	ItemCode	QtyOrd
S1	A1	10
S1	B1	15
S1	A2	12
S2	A1	20
S2	A2	11
S3	B1	14

I have another table which has columns of Item Code, Location Code and Quantity. This table has details of in which location a particular item is stored and in which quantity. Lets call this Inventory Table.

Location	ItemCode	QtyStr
L1	A1	5
L1	A2	3
L1	B1	15
L2	A1	7
L2	A2	8
L2	B1	10
L3	A1	2
L3	A2	4
L3	B1	5

Now I want calculate --> For each sales order, for each item in that order, how many locations are required to fulfill that item requirement in that order. The locations with highest quantity should be checked first. So want to have a calculated column in SalesOrder where each row has the number of rows from Inventory to sum up the QtyOrd value(starting from the location with highest quantity).
Also, I want another column with location names which will fulfll that item order.
I hope I have explained my question clearly.

Kindly help amitchandak Anonymous Jihwan_Kim daxer-almighty PaulOlding Fowmy

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Sun, 29 Aug 2021 15:38:59 GMT

manuvats Each each row in SalesOrder independent of the other rows or are they dependent? In other words, if for the S1 A1 10 in your first table, I calculate thate I need L1 and L2, can I no longer use the L1 for A1 items like for the S2 A1 20 line?

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

manuvats — Sun, 29 Aug 2021 15:47:14 GMT

That is a good question and I haven't thought of that. I will ask the concerned team. For the time being, please treat fulfillment of orders independent of each other. The items in a particular location are replenished quickly after fulfillment of an order.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Sun, 29 Aug 2021 16:38:34 GMT

manuvats OK, it is a bit messy and I will explain what is going on. Column is for the count and Column1 is for the list of locations. Now, DAX isn't great at sort orders, in fact by and large sort order is not guaranteed when dealing with DAX. We really can't use EVALUATE and ORDER BY in Power BI Desktop. However, CONCATENATEX does provide us something that can guarantee sort order. So, basically, I create SearchText that concatenates our locations together in the correct sort order by qty. Now, since I used CONCATENATEX, I need to convert things back into a table of values. That's the text to table pattern and essentially that is encapsulated in the calculation of __Table. So you can kind of ignore that because that's what that is doing. Now, for the Table1 variable, I am taking my converted list of Locations in a table in the correct sort order and I first add a running total so that I know how much inventory I have at that location including the inventory at locations with more inventory than the current location. I then add a column to decrement the sales order amount. Next, __Index finds the minimum index of the location where the decrement is still greater than or equal to zero. This is the first location where the running total meets the demand. After that it is straight-forward although you do have to account for the condition when the inventory does not meet the order amount.

Column = VAR __ItemCode = [ItemCode] VAR __QtyOrd = [QtyOrd] VAR __Separator = "," VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC) VAR __Len = LEN(__SearchText) VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1 VAR __Table = ADDCOLUMNS( ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Word", VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1)) VAR __Start = SWITCH(TRUE(), __Count = 1,1, [Value] = 1,1, FIND("|",__Text)+1 ) VAR __End = SWITCH(TRUE(), __Count = 1,__Len, [Value] = 1,FIND("|",__Text) - 1, [Value] = __Count,__Len, FIND(__Separator,__Text,__Start)-1 ) VAR __Word = MID(__Text,__Start,__End - __Start + 1) RETURN __Word ), "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr]) ) VAR __Table1 = ADDCOLUMNS( ADDCOLUMNS( __Table, "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr]) ), "__Decrement",[__RunningTotal] - __QtyOrd) VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value]) VAR __Locations = COUNTROWS(FILTER(__Table1,[Value] <= __Index)) RETURN IF(ISBLANK(__Locations),COUNTROWS(DISTINCT('Inventory'[Location])),__Locations)

Column 2 = VAR __ItemCode = [ItemCode] VAR __QtyOrd = [QtyOrd] VAR __Separator = "," VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC) VAR __Len = LEN(__SearchText) VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1 VAR __Table = ADDCOLUMNS( ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Word", VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1)) VAR __Start = SWITCH(TRUE(), __Count = 1,1, [Value] = 1,1, FIND("|",__Text)+1 ) VAR __End = SWITCH(TRUE(), __Count = 1,__Len, [Value] = 1,FIND("|",__Text) - 1, [Value] = __Count,__Len, FIND(__Separator,__Text,__Start)-1 ) VAR __Word = MID(__Text,__Start,__End - __Start + 1) RETURN __Word ), "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr]) ) VAR __Table1 = ADDCOLUMNS( ADDCOLUMNS( __Table, "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr]) ), "__Decrement",[__RunningTotal] - __QtyOrd) VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value]) VAR __Locations = FILTER(__Table1,[Value] <= __Index) VAR __Text1 = CONCATENATEX(__Table1,[__Word],",",[__QtyStr],DESC) VAR __Text = CONCATENATEX(__Locations,[__Word],",",[__QtyStr],DESC) RETURN IF(ISBLANK(__Text),__Text1,__Text)

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Sun, 29 Aug 2021 23:03:50 GMT

Here's a file with a full solution that can be adjusted to your heart's content. It should be faster than Greg_Deckler's formulas and is much more customizable. Not to mention it's infinitely easier to understand 🙂 without any flimsy string manipulation. Just download and watch the beauty of the solution 🙂

https://1drv.ms/u/s!ApyQEauTSLtOgZlOIZRShgVXkPOZnw?e=pVzdFb

Enjoy!

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Sun, 29 Aug 2021 23:19:43 GMT

daxer-almighty Well, you used a calculated table even though you hid it, it is still there. Not sure how you would prove performance or fragility one way or another. But, it's essentially using exactly the same technique as mine using GENERATESERIES, CONCATENATEX, etc., etc. Fundamentally it is no different except you incorporate your table in the model versus mine is in a VAR. Which, if we were to get into a debate about which one took up more space in the model...

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

learner03 — Mon, 30 Aug 2021 04:29:17 GMT

Greg_Deckler Hi Greg, how to modify above if we take the case that the items are not replenished quickly and use the fulfilment criteria in which the sales order arrive (S1 first andthen S2...so on ascending order)in the warehouse? example- S1 is using stock of A1 from L1 and L2 and only 2 is left in L1

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 10:00:11 GMT

Greg_Deckler

Yes. I did use a calc table but I don't have any remorse 🙂 Such a table should be, of course, created in PQ or in the source (you know the story). But I didn't have time (was too tired, it was deep into the night) to move this logic into PQ. But it's totally doable in there and is the right thing to do.

Second, DAX that has to operate on strings and does a lot of mangling-jumbling on them is fragile by definition. It's really for a reason that RMDB's store data in atomic portions, or at least they should if the person who builds them has any idea about a well-designed system. Here you've got the same story. After all, a tabular cube IS a database, albeit tabular (which does not change a single bit).

Third, where the heck did you see GENERATESERIES in my solution? 🙂 My code has no mention of it. On top of that, my code is infnitely more readable than yours, which also means it's more maintainable, customizable and user-friendly. This, of course, counts just as it counts in Python. And productivity counts, too. Just as it does in Python. It is for a reason that Python with its "strange" and strict formatting rules has become the most widely used language in the world in just too many fields to mention.

Fourth, I'm not sure about the space requirements. Maybe it does play a role, maybe it does not. But as long I don't have to save on storage/RAM, I always choose a model that is easier to maintain, understand and more flexible. It's exactly the same as it is for any programming task: First, make it correct, then make it fast, then (maybe) think about space... But time comes first these days and you know it. Also the ease with which people can grasp the code and make changes to it is of paramount importance. One should never lose readability/clarity of code from sight. Never. This is actually what distinguishes an excellent programmer from a programmer. "Any fool can write code that a machine can understand. It takes a good programmer to write code that people can understand." Rings a bell? 🙂 It should.

If the person who got the solution says later that it takes too much space in RAM (highly improbable but possible), well, then I'll try to think about a different solution. You also know the saying: "Premature optimization is the root of all evil." (quote by Sir Tony Hoare, popularized by Donald Knuth).

One last thing... It generally is much better (and advisable) to sacrifice memory for faster, more maintainable, more readable code.

I took a look at this UGLINESS:

VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC) VAR __Len = LEN(__SearchText) VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1

and I knew immediately it was not the correct approach. The code above is plain ugly. This is the first thing that comes to my mind when I (try to) read it and understand.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Mon, 30 Aug 2021 09:59:57 GMT

learner03 Yeah, I had a feeling that question was coming at some point and have been going back and forth on whether it starts to fall down the DAX recursion rabbit hole. Let me take a look.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 10:02:03 GMT

Well, Greg, it looks like PQ enters the stage 🤣🤣🤣

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Mon, 30 Aug 2021 10:30:08 GMT

daxer-almighty You missed the boundary case where there is not enough inventory to fulfill the order, your solution returns blank versus the correct answer of all locations.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 10:37:22 GMT

Greg_Deckler wrote:
daxer-almighty You missed the boundary case where there is not enough inventory to fulfill the order, your solution returns blank versus the correct answer of all locations.

Greg_Deckler, are you 100% sure? First, it would be highly inconsistent to do that since the number of locations would be 0 and the string would contain all of them... Not the greatest of designs, is it?

But, I've checked the original post:

"Now I want calculate --> For each sales order, for each item in that order, how many locations are required to fulfill that item requirement in that order. The locations with highest quantity should be checked first. So want to have a calculated column in SalesOrder where each row has the number of rows from Inventory to sum up the QtyOrd value(starting from the location with highest quantity).
Also, I want another column with location names which will fulfll that item order.
I hope I have explained my question clearly."

Where does it imply what you claim?

It says "How many locations are required to fulfill that item..." Returning 0 means "there is no number of locations that would". And since there aren't any, it just stands to reason that the string be empty/BLANK.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Mon, 30 Aug 2021 10:41:19 GMT

daxer-almighty If you checked my solution, my solution returns 3 for the location count and all the locations.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 10:49:46 GMT

Greg_Deckler

Please... mate. I don't exactly follow.

You're accusing my code of not adhering to what your code does? 🙂 Where did you get the rule from that stipulates that the lines which can't be fulfilled by any number of locations must be paired with all locations and the number must be 3 (the number of all eligible locations)? For the time being I can see that it's a rule you've invented yourself for some strange reason.

This rule of yours has 2 issues:

1. It makes the design inconsistent. How, without making calculations, can you distinguish between lines that CAN be fulfilled by all locations only and the ones that can't be fulfilled by any number of locations?

2. This rule does not exist in the original specification.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Mon, 30 Aug 2021 10:56:01 GMT

daxer-almighty Incorrect yet again I am afraid. No need for PQ. learner03 the following lists the number of locations taking into account a FIFO procedure for the sales orders. It is a minor tweak of the original solution. In this case, if you run out of locations capable of fulfilling the orders then it returns 0 locations capable of fullfilling the complete order. Could be tweaked for partial order fulfillment.

Column 3 = VAR __ItemCode = [ItemCode] VAR __Order = [Index] VAR __QtyOrd = SUMX(FILTER('SalesOrders',[Index]<=__Order && [ItemCode]=__ItemCode),[QtyOrd]) VAR __Separator = "," VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC) VAR __Len = LEN(__SearchText) VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1 VAR __Table = ADDCOLUMNS( ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Word", VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1)) VAR __Start = SWITCH(TRUE(), __Count = 1,1, [Value] = 1,1, FIND("|",__Text)+1 ) VAR __End = SWITCH(TRUE(), __Count = 1,__Len, [Value] = 1,FIND("|",__Text) - 1, [Value] = __Count,__Len, FIND(__Separator,__Text,__Start)-1 ) VAR __Word = MID(__Text,__Start,__End - __Start + 1) RETURN __Word ), "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr]) ) VAR __Table1 = ADDCOLUMNS( ADDCOLUMNS( __Table, "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr]) ), "__Decrement",[__RunningTotal] - __QtyOrd) VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value]) VAR __Locations = COUNTROWS(FILTER(__Table1,[Value] <= __Index)) RETURN __Locations+0

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Mon, 30 Aug 2021 11:01:11 GMT

daxer-almighty Granted, it's a business decision and could be open to interpretation given the original ground rules, all locations would be required to fulfill that order. Sure, additional inventory would be required but, sticking to the letter of the law and not making assumptions, the original ground rules would be that all locations should be returned.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 11:15:36 GMT

Greg_Deckler

I understand that now you've acknowledged, albeit indirectly (sadly), that the specification did not say a single word about returning all locations (and 3 - [EDT: it was 0 here orginally but, of course, it was a mistake]) when no number of locations could fulfill the requirement. That's good in any case.

I'd like to ask you a question. Do you consider the code you've written beautiful and readable to a person that comes to this problem the very first time? On a scale from 1 (ugly like hell) to 5 (beautiful), which score would you assign to your code? But honestly.

Thanks.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

Greg_Deckler — Mon, 30 Aug 2021 11:15:48 GMT

daxer-almighty Ah but beauty is in the eye of the beholder. I consider the code well formatted, progressing in a logical thought process and broken into debuggable chunks of code. Above all, I consider the code to return the correct answer and be flexible enough to handle changing requirements like FIFO, LIFO, etc. with relative ease. It is a small thing once a solution has been found to improve upon that solution. The hard thing is finding the solution in the first place. And often finding a solution requires cracking some eggs.

Since you are so fond of Nietzsche

"I want to learn more and more to see as beautiful what is necessary in things; then I shall be one of those who make things beautiful. Amor fati: let that be my love henceforth! I do not want to wage war against what is ugly. I do not want to accuse; I do not even want to accuse those who accuse. Looking away shall be my only negation."

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 11:31:41 GMT

Greg_Deckler, learner03, manuvats

Guys, I would really like to know if the code that Greg_Deckler has given you will ever return on a real model. My suspicion is that it'll last for quite a time on a decent model. Might not even return. Seen such things in the past with calculated columns on big fact tables. Have even troubleshot such isuses here on this forum.

Please run this code on a real model with a SalesOrder table that has hundreds of millions of rows or whatever you've got (but make it real). Let's see whether I'm right or wrong.

Re: Calculate number of top rows with sum of their values equal to a particular dynamic value

daxer-almighty — Mon, 30 Aug 2021 11:24:56 GMT

Greg_Deckler wrote:
daxer-almighty Granted, it's a business decision and could be open to interpretation given the original ground rules, all locations would be required to fulfill that order. Sure, additional inventory would be required but, sticking to the letter of the law and not making assumptions, the original ground rules would be that all locations should be returned.

It would be a very stupid business decision to return all locations and their number for lines that can't be fulfilled by any number of locations... Honestly, I wouldn't like to work for such a business 🤣