Forum Discussion
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 whether I am ordering enough material from suppliers against customer orders.
Therefore, it is crucial in the last column for each supplier; I am calculating percentage based on respective customer demand.
Using (Supplier Release -Customer Order)/Customer Order
For example,
Supplier Part A, B in green will be calculated against Part A
Supplier Part C, D, I in blue will be calculated against Part B
Supplier Part F,E,C in orange will be calculated against Part C
Any help is appreciated
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
6 Replies
- Fowmy
Super User
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 - Greg_Deckler
Community Champion
lotus22 - Sample data as text would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.
That said, you likely want to use something like EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
lotus22Thanks 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% - lotus22
The expected output is to calculate the percent of supplier release order with the respective customer demand
