cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Relate the subsequent rows to previous row

2020-07-18 10_35_32-Window.jpgSample 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

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@lotus22 

Please check if my solution answers your question: https://1drv.ms/u/s!AmoScH5srsIYgYF2GUtJ6SOoDK46Zw?e=v669SE
It's a Power Query solution.


Fowmy_0-1595088992849.png|

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

@lotus22 

Please check if my solution answers your question: https://1drv.ms/u/s!AmoScH5srsIYgYF2GUtJ6SOoDK46Zw?e=v669SE
It's a Power Query solution.


Fowmy_0-1595088992849.png|

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Awesome! Very creative!

@lotus22 

You are most welcome!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks Greg, here is the sample data, hope it load now.

CustomerPartSupplierPartTypeW1W2W3W4W5W6W7W8W9W10TotalsCalculations
Part A Customer Order56767787877677889999666677776373 
Part ASupplier Part ASupplier Release6666766780778807788866688005470-14%
Part ASupplier Part BSupplier Release677777867888998978889899899599110%
Part B Customer Order778889997778999999777897778819690 
Part BSupplier Part CSupplier Release8089989786000200010008000800899199441%
Part BSupplier Part DSupplier Release03109401250143014901170225001030987050%
Part BSupplier Part ISupplier Release000560780589040000100242%
Part C Customer Order3515131116966993 
Part CSupplier Part FSupplier Release56725889999952%
Part CSupplier Part ESupplier Release56202533912991019%
Part CSupplier Part CSupplier Release888883888875-19%

The expected output is to calculate the percent of supplier release order with the respective customer demand

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors