Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi
I have table of Packing List for shipments as follows -
PALLET ID | CARTON ID | ITEM ID | QTY |
10001 | M12300_22 | 12345 | 60 |
10001 | M12301_22 | 12345 | 60 |
10001 | M12302_22 | 12345 | 36 |
10001 | M12309_22 | 45678 | 60 |
10001 | M12310_22 | 45678 | 60 |
10002 | M12315_22 | 12345 | 60 |
10002 | M12320_22 | 67890 | 144 |
The Requirement is to create a Packing list Table Visual in following template -
PALLET ID | CARTON FROM | CARTON TO | ITEM ID | NO. OF CARTONS | QTY PER CARTON | TOTAL QTY |
10001 | M12300_22 | M12301_22 | 12345 | 2 | 60 | 120 |
10001 | M12302_22 | M12302_22 | 12345 | 1 | 36 | 36 |
10001 | M12309_22 | M12310_22 | 45678 | 2 | 60 | 120 |
10002 | M12315_22 | M12315_22 | 12345 | 1 | 60 | 60 |
10002 | M12320_22 | M12320_22 | 67890 | 1 | 144 | 144 |
The Logic for above presentation being -
If Pallet ID, item Id and qty in the carton are same and cartons are in sequence, they would be consolidated in the same row as we can see with red font rows. else they would be in independent rows as we can see in the other Black font rows.
I have been able to reach part of the Goal but need help to proceed further as explained below -
(1) To satisfy the first critera of Pallet, item, qty to be same, I created a new column concatenating these fields to create a key as follows -
Pallet_ID/Item_ID/Qty
and the field values appear as 10001/12345/60
(2) Then I created a reference table in power query grouping on above key fields and then created an index column with which i got a ranking for the rows where the rowkey had the same value.
(3) However, this will not check for cartons to be in sequence due to which the end result cannot be obtained.
Can someone advise on what further steps would be ?
regards
Hi, @GVTioluxe ;
You could refer to this.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Were u abe to find out why i had an error using the same measures u had shared?
regards
Hi, @GVTioluxe ;
Create measures:
IDNO = COUNT([CARTON ID])
TOTAL QTY = SUMX(SUMMARIZE('Table',[PALLET ID],[ITEM ID],[QTY],"1",SUM([QTY])),[1])
FROMCARTON = CALCULATE(MIN('Table'[CARTON ID]))
TOITEM = MAX('Table'[CARTON ID])
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yalan Wu
Thansk for your solution, which fits my need.
However when i applied it on the actual data i am not getting the right data being displayed.
The Result
The Source Data displayed in a Table visual for cross checking the result -
I am sorry couldnt attach the PBix as there is no provision here.
Also, can u pls help explain the solution, measures u have created so that I can undertsand the concept better in terms of how the sequencing was done.
regards
User | Count |
---|---|
120 | |
69 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |