cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PBIUWO Frequent Visitor
Frequent Visitor

Sum of quantity filtered by the Earliest date per Order

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. 

 

12.PNG

 

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,

1 ACCEPTED SOLUTION

Accepted Solutions
PBIUWO Frequent Visitor
Frequent Visitor

Re: Sum of quantity filtered by the Earliest date per Order

I have solved this on my own. 

 

Thanks

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Sum of quantity filtered by the Earliest date per Order

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PBIUWO Frequent Visitor
Frequent Visitor

Re: Sum of quantity filtered by the Earliest date per Order

Order NumberItem CodeShipping IDQTYShip Date
1A112331/2/2019
1A212331/2/2019
1B323441/6/2019
2B425551/5/2019
2B625551/5/2019
2D534421/8/2019
2F534421/8/2019
3G164411/9/2019
3H445621/10/2019
3H645631/10/2019

 

 

 

Expected result, 

 

Order NumberItem CodeShipping IDQTYShip DateExpected Result
1A112331/2/20196
1A212331/2/20196
1B323441/6/20196
2B425551/5/201910
2B625551/5/201910
2D534421/8/201910
2F534421/8/201910
3G164411/9/20191
3H445621/10/20191
3H645631/10/20191
PBIUWO Frequent Visitor
Frequent Visitor

Re: Sum of quantity filtered by the Earliest date per Order

I have solved this on my own. 

 

Thanks

View solution in original post

Community Support Team
Community Support Team

Re: Sum of quantity filtered by the Earliest date per Order

@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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 147 members 1,675 guests
Please welcome our newest community members: