Power BI- multiple values

[mwm39]

Hi All,

I have a table with items and specification (Length, width, Height, weight) and Carton used . Ideally items should show same specification and Carton used in all transactions.

However sometime due to system /user error (user selecting different carton) , some items appear to have numerous spec and carton used. For example:

Item             L        H     W         Carton Used

101               1        1    1             W1

102               2       2      2            W2

103               3      3      3             W3

101               1      1      2             W1

102                2      2     2             W1

In above all items 101,102,103 have correct specification from line 1 to 3. However line 4 and 5 showing incorrect value (highligheted in red). How can I identify records which is deviating from its standard specification. We have nearly 3500 items and roughly 25000 lines generated every day.  Any help would be highly appreciated. Thanks 

[Anonymous · Accepted solution]

Hi @mwm39 ,

You can apply the below codes in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRQsbhhkqxOiAJIyAHGYcbQSWMgRxkHG4MlUA2xgivUUCJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, L = _t, H = _t, W = _t, #"Carton Used" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"L", Int64.Type}, {"H", Int64.Type}, {"W", Int64.Type}, {"Carton Used", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Corrected W", each if [L]=[H] and [L]=[W] and [H]=[W] then [H] else Number.Round(([L]+[H]+[W] )/3)),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Corrected Carton Used", each if [L]=[H] and [L]=[W] and [H]=[W] then "W"&Number.ToText([H]) else "W"&Number.ToText(Number.Round(([L]+[H]+[W] )/3)))
in
    #"Added Custom"

Best Regards

[mwm39]

Yes we have master files for volume specification but not for carton because for example customer order two items together than caarton would be different if they order items seperately.  For Carton I just need to identify all Carton types being used for items. For example

item       Qunatity    Carton type

101           1                   W1

101            1                   W1

101             1                   W3

I want to identify that if with 1 quantity majority used W1 and what other Cartons were used as well. Hope its clear. Thanks

[CoreyP]

You can use a RANKX function to rank the item and quantity by count of carton type. Then you can filter or highlight rows where the rank does not equal 1. 

[CoreyP]

Do you have an item master table which contains what the carton size should be?

[mwm39]

Yes we have master files for volume specification but not for carton because for example customer order two items together than caarton would be different if they order items seperately.  For Carton I just need to identify all Carton types being used for items. For example

item       Qunatity    Carton type

101           1                   W1

101            1                   W1

101             1                   W3

I want to identify that if with 1 quantity majority used W1 and what other Cartons were used as well. Hope its clear. Thanks

[CoreyP]

Or are you calculating what the carton size should be? For instance, highest number of occurences for carton size is considered what it should be?

[mwm39]

For instance, highest number of occurences for carton size is considered what it should be?

this is 100% accurate, highest occurences is what it should be