Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

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:

Annotation 2020-07-28 160633.png

For the updated .pbix file,pls see attached.

 


 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

8 REPLIES 8
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-07-27 151205.png

For the related .pbix file,pls see attached.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
edhans
Super User
Super User

@Anonymous - what exactly are you tryng to do? just return the summary table you've shown? if so, these measures will do it.

edhans_0-1595811478219.png

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.





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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 IDShipment DateWeight KGShipment location
1100005807/18/202020Vik, IND
2100005877/18/202010KAN,IND
3100006137/18/202080GUJ, IND
4100006517/18/202030KAR,IND
5100007277/18/202040BHA, IND
6100009527/18/202030GOR,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.

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:

Annotation 2020-07-27 163406.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Hi Kelly, 

 

Thanks, its giving me the output i want by measure. But, its working only in table visual. 🙂

 

I am trying to implement same formula in calculated column in order to show it in Clustered Column chart but its giving me wrong output. Can you please help how to do it.       

Thanks

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:

Annotation 2020-07-28 160633.png

For the updated .pbix file,pls see attached.

 


 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Thanks much Kelly. 😊

harshnathani
Community Champion
Community Champion

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

 

 

1.jpg

 

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.