Calculating based on a column value

[stefani_vileva]

Hello everyone,

I have the following problem. I have a only table in which I have the following columns: Order number, product, boxes, order date. Per one day we can have more than 1 order in which some of the articles may appear in the both orders but in some case only in the one. Based on the order number we can identify which one came first, which one second and so on.

The goal is to calculate the sum of boxes for the first order, and the sum of the boxes for all other orders.

For example let we have the following scenario:

In this case, in the result we would have
Product | Or. Box | Ex. Box
x            |   20      |   0
y            | 12        |   5
z            |  3         |   0
w           |   12      |   0

I am using the following dax, but unfortunately it doesnt work when i have more than 1 date selected.

Extra boxes = CALCULATE(SUM(Orders[Boxes]), KEEPFILTERS(Orders[Date]), FILTER(Orders, Orders[Order] > MIN(Orders[Order])))

Original boxes = CALCULATE(SUM(Orders[Boxes]), KEEPFILTERS(Orders[Date]), FILTER(Orders, Orders[Order] = MIN(Orders[Order])))

Here is the link of the file .

Thank you for your help.

Kind regards,

Stefani Vileva

[stefani_vileva · Accepted solution]

I have solved the problem in the following way. First I have made a duplicate table in which I have groupped the data based on the dates for each date what is the min and the max order number. After that, using the shown measures I have calculated the values for original and extra boxes. Note on the creating the table for min and max order number, sometimes maybe there could be one order per day without an extra order, in that case I have filtered the table not to take those rows because in that case the values would be summed in both of the measures.

Extra boxes = CALCULATE(
    SUM(Orders[Boxes]), 
    Orders[Order] IN VALUES('Groupped Orders'[MAX_ORDER])
)

Original boxes = CALCULATE(
    SUM(Orders[Boxes]), 
    Orders[Order] IN VALUES('Groupped Orders'[MIN_ORDER])
)

[v-easonf-msft]

Hi, @stefani_vileva 

It's pleasant that your problem has been solved. Thanks for your sharing.

Best Regards,
Community Support Team _ Eason