Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jakeudy
Helper I
Helper I

Grouping rows of data so that each group sums to a specific quantity

I have a table of data that I need to group in a way that allows me to create a production schedule. Below is the current structure of the table:

 

Sales Order Ship No Later ThanQuantity
SO-24-01767 05/24/20241
SO-24-01775 05/24/20241
SO-24-01789 06/07/20241
SO-24-01792 06/21/20244
SO-24-01795 06/28/20241
SO-24-01799 07/12/20241
SO-24-01823 07/12/20241
SO-24-01831 07/16/20243
SO-24-01833 07/16/20241
SO-24-01840 07/16/20241
SO-24-01842 07/22/20241
SO-24-01859 07/22/20242
SO-24-01860 07/26/20241
SO-24-01866 07/26/20241
SO-24-01869 08/09/20243
SO-24-01874 08/09/20241
SO-24-01878 08/23/20241
SO-24-01882 08/23/20244
SO-24-01890 08/30/20241

 

What I need to do is set up a recursive calculation to group these rows such that each group adds up to a quantity of 3, and have it sum in chronological order based on the ship no later date. I need each of the groups to be defined by a week number, starting with the current week. Below is my expected output.

 

Sales OrderWeek 24, 2024Week 25, 2024Week 26, 2024Week 27, 2024Week 28, 2024Week 29, 2024Week 30, 2024Week 31, 2024Week 32, 2024Week 33, 2024
SO-24-017671         
SO-24-017751         
SO-24-017891         
SO-24-01792 1        
SO-24-01792 1        
SO-24-01792 1        
SO-24-01792  1       
SO-24-01795  1       
SO-24-01799  1       
SO-24-01823   1      
SO-24-01831   1      
SO-24-01831   1      
SO-24-01831    1     
SO-24-01833    1     
SO-24-01840    1     
SO-24-01842     1    
SO-24-01859     1    
SO-24-01859     1    
SO-24-01860      1   
SO-24-01866      1   
SO-24-01869      1   
SO-24-01869       1  
SO-24-01869       1  
SO-24-01874       1  
SO-24-01878        1 
SO-24-01882        1 
SO-24-01882        1 
SO-24-01882         1
SO-24-01882         1
SO-24-01890         1

 

Is this possible to accomplish in Power Query? I have tried several different approaches and none of the things I have tried have produced the desired output. The orders that have a greater quantity than 1 that have to be split across multiple weeks has presented a challenge. Any help would be much appreciated.

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@jakeudy 

result : 

Daniel29195_0-1718451719481.png

 

 

check attached file

View solution in original post

2 REPLIES 2
DataNinja777
Super User
Super User

Hi @jakeudy 

There are multiple ways to produce your required output.  In addition to the elegant dax solution provided by @Daniel29195 , let me also provide alternative method which uses power query as well as dax to produce your requried output.  

In order to create different rows for multiple quantities, you can create add column for each multiple quantities and unpivot those added columns.  (The way I did it is not so efficient as you have to add column one by one for additional multiple quantities, and there's probably quicker way to do this as well as ensuring that the model still works for quantities over 5).  

Then you add an index column and a modulo to identify multiples of 3, such as 3, 6, 9, 12, and so on, then do fill up, and divide that by 3 to get the 111, 222, 333, 444, and so on.  

After that, you can summarize that column using dax and reference the minimum week number from the calendar table, and the resultant output will look like below.  

DataNinja777_0-1718454382780.png

I attach a pbix file as an example.  

 

Daniel29195
Super User
Super User

@jakeudy 

result : 

Daniel29195_0-1718451719481.png

 

 

check attached file

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.