The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Grouping rows of data so that each group sums to a...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

06-14-2024
01:59 PM

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 Than | Quantity | |

SO-24-01767 | 05/24/2024 | 1 | |

SO-24-01775 | 05/24/2024 | 1 | |

SO-24-01789 | 06/07/2024 | 1 | |

SO-24-01792 | 06/21/2024 | 4 | |

SO-24-01795 | 06/28/2024 | 1 | |

SO-24-01799 | 07/12/2024 | 1 | |

SO-24-01823 | 07/12/2024 | 1 | |

SO-24-01831 | 07/16/2024 | 3 | |

SO-24-01833 | 07/16/2024 | 1 | |

SO-24-01840 | 07/16/2024 | 1 | |

SO-24-01842 | 07/22/2024 | 1 | |

SO-24-01859 | 07/22/2024 | 2 | |

SO-24-01860 | 07/26/2024 | 1 | |

SO-24-01866 | 07/26/2024 | 1 | |

SO-24-01869 | 08/09/2024 | 3 | |

SO-24-01874 | 08/09/2024 | 1 | |

SO-24-01878 | 08/23/2024 | 1 | |

SO-24-01882 | 08/23/2024 | 4 | |

SO-24-01890 | 08/30/2024 | 1 |

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 Order | Week 24, 2024 | Week 25, 2024 | Week 26, 2024 | Week 27, 2024 | Week 28, 2024 | Week 29, 2024 | Week 30, 2024 | Week 31, 2024 | Week 32, 2024 | Week 33, 2024 |

SO-24-01767 | 1 | |||||||||

SO-24-01775 | 1 | |||||||||

SO-24-01789 | 1 | |||||||||

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2024
04:43 AM

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2024
05:32 AM

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.

I attach a pbix file as an example.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2024
04:43 AM