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

The 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.

Reply
GVTioluxe
Helper I
Helper I

ranking records based on evaluating consecutiveness of field values

Hi

 

I have  table of Packing List for shipments as follows - 

 

PALLET IDCARTON IDITEM IDQTY
10001M12300_221234560
10001M12301_221234560
10001M12302_221234536
10001M12309_224567860
10001M12310_224567860
10002M12315_221234560
10002M12320_2267890144

 

 

The Requirement is to create a Packing list Table Visual in following template - 

PALLET IDCARTON FROMCARTON TOITEM IDNO. OF CARTONSQTY PER CARTONTOTAL QTY
10001M12300_22M12301_2212345260120
10001M12302_22M12302_221234513636
10001M12309_22M12310_2245678260120
10002M12315_22M12315_22 1234516060
10002M12320_22M12320_22678901144144

 

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.

 

GVTioluxe_0-1653971096126.png

(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

 

 

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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.

Hi

 

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

 

regards

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1654247081353.png


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

 

GVTioluxe_0-1654567899903.png

The Source Data displayed in a Table visual for cross checking the result - 

 

GVTioluxe_1-1654568004539.png

 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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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