ranking records based on evaluating consecutiveness of field values

[GVTioluxe]

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

[v-yalanwu-msft]

Hi， @GVTioluxe ；

You could refer to this.

https://community.powerbi.com/t5/Desktop/How-to-compute-due-date-base-on-invoice-date-and-Credit-ter...

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.

[GVTioluxe]

Hi

Were u abe to find out why i had an error using the same measures u had shared?

regards

[v-yalanwu-msft]

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.

[GVTioluxe]

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