Relate the subsequent rows to previous row

[lotus22]

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

[Fowmy · Accepted solution]

@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

[lotus22]

Awesome! Very creative!

[Fowmy]

@lotus22 

You are most welcome!

[Greg_Deckler]

@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 data

3. 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/3395....

[lotus22]

Thanks 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