cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.