Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Awesome! Very creative!
@lotus22
You are most welcome!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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....
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% |
The expected output is to calculate the percent of supplier release order with the respective customer demand
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |