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.
Hi,
I am trying to create a way to measure what the % of quantity received is in the earliest Shipping Date by Order Number when compared to the total quantity of the Order Number.
I am trying to get the sum of the quantity (QTY) and to only take the Earliest date of the Shipping ID per Order.
Then to figure out how to sum the quanity (QTY) per Order Number. From this to be able to divide the two measurements and get the % average of the quantity received in the earliest Shipping ID/Shipping Date.
From this Example, Order Number, "1" has 2 different Shipping ID (123, and 234) that totals 10 QTY. The earliest Shipping Date is "1/2/2019" and is Shipping ID "123". So the quantity on the earliest date is 6, so the % of quantity received on the earliest date is 60%.
Thank you in Advance,
Solved! Go to Solution.
Hi,
Show the exact layout of your expected result. Also, share data in a format that can be pasted in MS Excel or share the link from where i can download your PBI file.
Order Number | Item Code | Shipping ID | QTY | Ship Date |
1 | A1 | 123 | 3 | 1/2/2019 |
1 | A2 | 123 | 3 | 1/2/2019 |
1 | B3 | 234 | 4 | 1/6/2019 |
2 | B4 | 255 | 5 | 1/5/2019 |
2 | B6 | 255 | 5 | 1/5/2019 |
2 | D5 | 344 | 2 | 1/8/2019 |
2 | F5 | 344 | 2 | 1/8/2019 |
3 | G1 | 644 | 1 | 1/9/2019 |
3 | H4 | 456 | 2 | 1/10/2019 |
3 | H6 | 456 | 3 | 1/10/2019 |
Expected result,
Order Number | Item Code | Shipping ID | QTY | Ship Date | Expected Result |
1 | A1 | 123 | 3 | 1/2/2019 | 6 |
1 | A2 | 123 | 3 | 1/2/2019 | 6 |
1 | B3 | 234 | 4 | 1/6/2019 | 6 |
2 | B4 | 255 | 5 | 1/5/2019 | 10 |
2 | B6 | 255 | 5 | 1/5/2019 | 10 |
2 | D5 | 344 | 2 | 1/8/2019 | 10 |
2 | F5 | 344 | 2 | 1/8/2019 | 10 |
3 | G1 | 644 | 1 | 1/9/2019 | 1 |
3 | H4 | 456 | 2 | 1/10/2019 | 1 |
3 | H6 | 456 | 3 | 1/10/2019 | 1 |
I have solved this on my own.
Thanks
Glad to hear that. By the way, you may share the solution here. Your contribution is highly appreciated.
Column = SUMX ( TOPN ( 1, FILTER ( Table1, Table1[Order Number] = EARLIER ( Table1[Order Number] ) ), Table1[Ship Date], ASC ), Table1[QTY] )
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 |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |