Forum Discussion
lotus22
Helper III
Helper IIIRelate the subsequent rows to previous row
Sample Data Above. I have customer orders, and in subsequent rows, there will be supplier orders. There could be 10-15 rows for supplier orders after each customer order. I need to calculate wh...
lotus22
Please check if my solution answers your question: https://1drv.ms/u/s!AmoScH5srsIYgYF2GUtJ6SOoDK46Zw?e=v669SE
It's a Power Query solution.
|If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
lotus22Helper III
Helper IIIThanks Greg, here is the sample data, hope it load now.
| CustomerPart | SupplierPart | Type | W1 | W2 | W3 | W4 | W5 | W6 | W7 | W8 | W9 | W10 | Totals | Calculations |
| Part A | Customer Order | 567 | 67 | 787 | 877 | 677 | 889 | 999 | 66 | 667 | 777 | 6373 | ||
| Part A | Supplier Part A | Supplier Release | 66 | 667 | 667 | 80 | 778 | 80 | 778 | 886 | 668 | 800 | 5470 | -14% |
| Part A | Supplier Part B | Supplier Release | 677 | 77 | 786 | 788 | 899 | 89 | 788 | 89 | 899 | 899 | 5991 | 10% |
| Part B | Customer Order | 77 | 88 | 89 | 997 | 7789 | 999 | 997 | 7789 | 777 | 88 | 19690 | ||
| Part B | Supplier Part C | Supplier Release | 80 | 89 | 98 | 978 | 6000 | 2000 | 1000 | 8000 | 800 | 899 | 19944 | 1% |
| Part B | Supplier Part D | Supplier Release | 0 | 310 | 940 | 1250 | 1430 | 1490 | 1170 | 2250 | 0 | 1030 | 9870 | 50% |
| Part B | Supplier Part I | Supplier Release | 0 | 0 | 0 | 56 | 0 | 78 | 0 | 5890 | 4000 | 0 | 10024 | 2% |
| Part C | Customer Order | 3 | 5 | 15 | 13 | 11 | 16 | 9 | 6 | 6 | 9 | 93 | ||
| Part C | Supplier Part F | Supplier Release | 5 | 6 | 7 | 25 | 8 | 8 | 9 | 9 | 9 | 9 | 95 | 2% |
| Part C | Supplier Part E | Supplier Release | 5 | 6 | 20 | 25 | 3 | 3 | 9 | 12 | 9 | 9 | 101 | 9% |
| Part C | Supplier Part C | Supplier Release | 8 | 8 | 8 | 8 | 8 | 3 | 8 | 8 | 8 | 8 | 75 | -19% |
lotus22Helper III
Helper IIIThe expected output is to calculate the percent of supplier release order with the respective customer demand