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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors