grouping orders

[Anonymous]

Hello  Community  -  

I have what I think is a simple question.    Using the calculated column formula below, my goal is to assign categories to Orders based on the total value of the order.    However, what is happening is that the formula is looking at the individual line of the order, rather than order value in total.     

For example, it is assigining (incorrectly), the category of "Quick Ship" to these lines below....because they are indeed below 40,000 dollars.   But actually the correct categorization of this should be "10 Day" because the value of the Order is above 40,000 and below 100,000.    

How can modify my formula to ensure it is taking the total sum of the Order  (based on Order ID)?

Shipment Category = IF('Shipped'[Shipped Price] <= 40000,"Quick Ship",IF('Shipped'[Shipped Price] > 40000 && 'Shipped'[Shipped Price] < 100000,"10 Day",IF('Shipped'[Shipped Price] >= 100000,"30 Day")))

[amitchandak · Accepted solution]

@Anonymous , You need to have order-level sum, inside this formula or as a new column.

example, both as new columns

order sum = sumx(filter('Shipped', [order no] = earlier([order no] )),[Shipped Price] )

Shipment Category =
switch( true(),
'Shipped'[order sum ] <= 40000,"Quick Ship",
'Shipped'[order sum ] < 100000,"10 Day",
"30 Day"
)

[Anonymous]

Hi @Anonymous ,

Yes, the calculated column is by individual line, but not order.

If you want to sum it based on order id, you may try to use the below calculated measure:

Measure = calculate(sum(your_value),allexcept(your_table,order_id)).

Please try.

Aiolos Zhao