Forum Discussion
grouping orders
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)?
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"
)
2 Replies
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"
)