Forum Discussion
Weight distribution for shipments
Hi Team,
I have Shipment data as below. "Shipment id" is unique and there can be multiple shipments in a day.
Now we can ship to multiple location in a day but the condition is we can ship maximum 70KG per shipment.
Shipment ID | Shipment Date | Weight KG | Shipment location |
10000580 | 7/18/2020 | 20 | Vik, IND |
10000587 | 7/18/2020 | 10 | KAN,IND |
10000613 | 7/18/2020 | 80 | GUJ, IND |
10000651 | 7/18/2020 | 30 | KAR,IND |
10000727 | 7/18/2020 | 40 | BHA, IND |
10000952 | 7/18/2020 | 30 | GOR,IND |
10000973 | 7/19/2020 | 20 | Vik, IND |
10001038 | 7/19/2020 | 15 | KAN,IND |
10001083 | 7/20/2020 | 20 | GUJ, IND |
10001106 | 7/20/2020 | 15 | KAR,IND |
10001252 | 7/21/2020 | 25 | BHA, IND |
10001264 | 7/21/2020 | 20 | GOR,IND |
10001290 | 7/21/2020 | 30 | Vik, IND |
10001342 | 7/21/2020 | 40 | KAN,IND |
10001385 | 7/21/2020 | 90 | GUJ, IND |
10001405 | 7/22/2020 | 50 | KAR,IND |
10001485 | 7/22/2020 | 10 | BHA, IND |
I have created summary table as below. In the below table for two dates shipment is above 70 KG hence i would need to divide those shipments.
Shipment Date | Weight/day | >70 Kg? |
07/18/2020 | 210 | Yes |
07/19/2020 | 35 | No |
07/20/2020 | 35 | No |
07/21/2020 | 205 | Yes |
07/22/2020 | 60 | No |
Can anyone help me to understand how can I do it. Thanks in advance.
Hi Anonymous ,
Yes,create a calculated column as below:
Column = var _total=SUMX(FILTER('Table','Table'[Shipment ID]<=EARLIER('Table'[Shipment ID])&&'Table'[Weight KG]<70&&'Table'[Shipment Date]=EARLIER('Table'[Shipment Date])),'Table'[Weight KG]) var _maxvalue=MAXX(FILTER('Table','Table'[Shipment Date]=EARLIER('Table'[Shipment Date])),'Table'[Weight KG]) Return IF('Table'[Weight KG]>70,"shipment 1",IF(_maxvalue<70 &&_total<70,"shipment 1",IF(_maxvalue<70&&_total>70&&_total<140,"2",IF(_maxvalue>70 &&_total<70,"shipment 2",IF(_maxvalue>70&&_total>70&&_total<140,"shipment 3" )))))Then you will see:
For the updated .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
8 Replies
HI Anonymous ,
Not very clear as to what you want to Divide.
But let me know if this is what you want.
Divide greater than 70 = var __prevdatethan70= CALCULATE(MIN('Table'[Shipment Date]),FILTER('Table','Table'[Weight/day] > 70 && 'Table'[Shipment Date] > EARLIER('Table'[Shipment Date]))) var __prevgreaterthan70 = CALCULATE(MAX('Table'[Weight/day]),FILTER('Table','Table'[Shipment Date] = __prevdatethan70)) RETURN IF('Table'[Weight/day]>70, DIVIDe('Table'[Weight/day],__prevgreaterthan70),'Table'[Weight/day])Regards,
Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!- edhans
Community Champion
Anonymous - what exactly are you tryng to do? just return the summary table you've shown? if so, these measures will do it.
Total Weight = SUM('Table'[Weight KG]) Too Much Weight = IF( [Total Weight] > 70, "Yes", "No" )However, if you are trying to split the shipments, then how? In order of the Shipping ID? In order of the weight? In an optimized method that minimizes the shipments, getting them as close to 70KG as possible? How do you handle the single shipments that are 80 and 90KG, already above your 70K limit.
So many questions, and some of the above would be a pretty large undertaking, not a simple measure.Sorry for not being clear.
Thanks for revert. The measure you shown I have created it but not able to achieve the output which i want.
As you rightly asked, yes I am looking for spliting /clubbing multiple shipments into one in order to minimize & getting them as close to 70KG as possible. Not basis on order id but on Weight for a specific date..
If we see below example, on 18th July there are 6 shipments. I want to club the shipments in such a way that should not be above 70 KG.
If single shipment is above 70KG then we can count it as another shipments.
Sr. No. Shipment ID Shipment Date Weight KG Shipment location 1 10000580 7/18/2020 20 Vik, IND 2 10000587 7/18/2020 10 KAN,IND 3 10000613 7/18/2020 80 GUJ, IND 4 10000651 7/18/2020 30 KAR,IND 5 10000727 7/18/2020 40 BHA, IND 6 10000952 7/18/2020 30 GOR,IND So, here we can club shipements as below,
Sr.no. 1, 2, 4 as "Shipment 1"
Sr. No. 3 as "Shipment 2"
Sr. No. 5, 6 as "Shipment 3"
Can we achieve this in any way?
Thanks.
v-kelly-msftCommunity Support
Hi Anonymous ,
Create a measure as below:
measure = var _total=SUMX(FILTER(ALL('Table'),'Table'[Shipment ID]<=MAX('Table'[Shipment ID])&&'Table'[Weight KG]<70&&'Table'[Shipment Date]=MAX('Table'[Shipment Date])),'Table'[Weight KG]) var _maxvalue=MAXX(FILTER(ALL('Table'),'Table'[Shipment Date]=MAX('Table'[Shipment Date])),'Table'[Weight KG]) Return IF(MAX('Table'[Weight KG])>70,"shipment 1",IF(_maxvalue<70 &&_total<70,"shipment 1",IF(_maxvalue<70&&_total>70&&_total<140,"2",IF(_maxvalue>70 &&_total<70,"shipment 2",IF(_maxvalue>70&&_total>70&&_total<140,"shipment 3" )))))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- v-kelly-msft
Hi Anonymous ，
Create one measure is enough:
Shipment DateWeight/day>70 Kg? = Var _total=SUMX(FILTER(ALL('Table'),'Table'[Shipment Date]=MAX('Table'[Shipment Date])),'Table'[Weight KG]) Return IF(_total>70,"Yes","No")And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
