Sum of quantity filtered by the Earliest date per Order

[PBIUWO]

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,

[Ashish_Mathur]

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.

[PBIUWO]

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

[PBIUWO · Accepted solution]

I have solved this on my own. 

Thanks

[v-chuncz-msft]

@PBIUWO 

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]
)