Get Sum of Quantity shipped first time by Customer

Hi,

Can someone please help with the following calculation?
I am working on a formula that calculates the sum of qty that got shipped first time for each sales order.
I have orders that got invoiced multiple times and need to find the qty of the very first time it got shipped for all orders (first time shipment = minimum invoice number)
I have the following formula which is working fine.

QtyShipped1stTime = calculate(sum(Invoices[sum(qty)]), Invoices[invoice_no] = MINX(Invoices, Invoices[invoice_no])

But now I need to calculate the total of QtyShipped1stTime by customer (Total of qty shipped 1st time for each order by customer)
How would I do that?

For example, I need to calculate the the sum of the amount of the minimum invoice number for each company:

image

So, for company A, the sum should be 800 (Company A, min of invoice number for order 1 = 100 + for compnay A, min of inv for order no. 3= 700 ==> 100+700 = 800 )
for company B, the sum should be 200
How can I get these results in DAX?

Thank you very much in advance!

Hello @gkhokher,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve provided. Below are the two simple measures alongwith the screenshot of the results provided for the reference -

First Sales By Min Order =
VAR _Min_Invoice =
CALCULATE( MIN( Data[Invoice] ) ,
    ALLEXCEPT( Data , Data[Company] , Data[Order] ) )

VAR _Total_First_Shipping_Amount =
SUMX(
    FILTER( Data ,
        Data[Invoice] = _Min_Invoice ) ,
    Data[Amount] )

RETURN
_Total_First_Shipping_Amount
First Sales By Min Order - Totals =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Data ,
            Data[Company] ,
            Data[Order] ,
            Data[Invoice] ) ,
        "@Totals" ,
        [First Sales By Min Order] ) ,
    [@Totals] )

Final Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Sales By Min Invoice Per Order Per Customer - Harsh.pbix (22.9 KB)

3 Likes

Hi @gkhokher, did the response provided by @Harsh help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Hi @EnterpriseDNA , I am still working on it. I did not get the expected numbers…

Thank you…

Hi @Harsh ,
Thank you very much for getting back to me.
I tried implementing the formula. But it is not showing me the expected results. When it is calculating the minimum invoice number, it is getting the minimum number of the invoice based on ALL sales order numbers.

It should be returning the minimum invoice based on the sales order number.
For example,

image

Right now, it is returning inv no = 1
But the result should be: for customer number 1, order number 1, minimum invoice is 1.
For customer number 1, order number 2, min invoice is 3.

Hope I was able to explain the issue.

Please let me know if questions.

Thank you very much for the support!

Hello @gkhokher,

I tried the same measure again based on the example data which you’ve showcased in the form of screenshot. And in my case, I’m getting the results as expected below is the screenshot provided -

First Sales By Min Order - As per Data 2 =
VAR _Min_Invoice =
CALCULATE( MIN( Data2[Invoice] ) ,
    ALLEXCEPT( Data2 , Data2[Company] , Data2[Order] ) )

VAR _Results =
SUMX(
    FILTER( Data2 ,
        Data2[Invoice] = _Min_Invoice ) ,
    Data2[Invoice] )

RETURN
_Results

Final Results

I’m not sure in what context you’re using this measure.

Thanks and Warm Regards,
Harsh

Sales By Min Invoice Per Order Per Customer - Harsh v2.pbix (25.4 KB)

1 Like

Hi @Harsh ,

It is working now. It was the context. I had another column from another table in the visual. When I removed it, I got the results.

Thank you very much for your support.