Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
5 years ago
Solved

Count Ordernumbers base on duplicate value

Hello,

 

I have following table:

OrdernumberDateStatus
888825.04.2021A
777725.04.2021A
111125.04.2021A
111125.04.2021D
222225.04.2021A
222225.04.2021D
333325.04.2021A
333325.04.2021D
444425.04.2021D
555525.04.2021D

Order numbers with D are the orders which we accomplished to ship out today already. Status A orders are the delayed leftovers from yesterday which "had" to become D later today. Thats the reason these orders have an "A" and an "D" counterpart. Thats the reason why orders with "A" only gets updated  once a day, otherwise they would be overwritten with "D" and you can't tell the real perfomance of today. "A" orders are not counting to the performance of today, because they should have finished yesterday.

 

So to calculate the performance of today istn't just count of D orders minus count of A orders. Its actually count all "D" orders minus the "D"orders which also has a "A" Status counterpart. 

In above sample data 2 should be the outcome. 

I hope it was not too confusing. 
Thank you in advance!
Best.

 

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • OwenAuger's avatar
    OwenAuger
    5 years ago

    Hello Applicable88 

     

    A measure like this should work:

    Performance = 
VAR Orders_D = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "D"
    )
VAR Orders_A = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "A"
    )
RETURN
    COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )

    The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.

     

    With your sample data,

    • Orders_D = { 1111, 2222, 3333, 4444, 5555 }
    • Orders_A = { 1111, 2222, 3333, 7777, 8888 }
    • EXCEPT ( Orders_D, Orders_A ) = { 4444, 5555 }

    Regards,

    Owen

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    5 years ago

    Hello Applicable88 

     

    A measure like this should work:

    Performance = 
VAR Orders_D = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "D"
    )
VAR Orders_A = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "A"
    )
RETURN
    COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )

    The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.

     

    With your sample data,

    • Orders_D = { 1111, 2222, 3333, 4444, 5555 }
    • Orders_A = { 1111, 2222, 3333, 7777, 8888 }
    • EXCEPT ( Orders_D, Orders_A ) = { 4444, 5555 }

    Regards,

    Owen

    • Applicable88's avatar
      Applicable88
      Impactful Individual
      5 years ago

      Thank you again OwenAuger .

       

      I found the problem. My last function was not right. A simple sum was enough and afterwards it worked out.

      Best. 

    • Applicable88's avatar
      Applicable88
      Impactful Individual
      5 years ago

      OwenAuger ! 

      Thank you very much it works for the ordernumbers. 

      I forgot to mention that I have another column (Positions of deliveries) which this time needs to be sum up base on the above logic, so only sum these delivery positions of the 2 orders. I tried with calculate sum and sumx as well, but I always get an error or empty value and sometimes a much bigger number. As you can see I changed the last line to sum instead of count:

       

      Performance = 
VAR Orders_D = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "D"
    )
VAR Orders_A = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "A"
    )
RETURN
calculate( SUMX(Orders,Orders[Deliverypositions]), EXCEPT ( Orders_D, Orders_A ) )

      Do I need a comlete another formula to get sum the position?

       

       
      I hope I can use same approach for the Deliverypositions as well.