Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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:
For the updated .pbix file,pls see attached.
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.
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry 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.
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.
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:
For the updated .pbix file,pls see attached.
Thanks much Kelly. 😊
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 Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |