cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stefani_vileva
Resolver I
Resolver I

Calculating based on a column value

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:

stefani_vileva_0-1653400251793.png

 

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

1 ACCEPTED SOLUTION
stefani_vileva
Resolver I
Resolver I

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])
)

 

View solution in original post

2 REPLIES 2
stefani_vileva
Resolver I
Resolver I

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])
)

 

Hi, @stefani_vileva 

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

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Top Solution Authors
Top Kudoed Authors