Largest values from a row

[Anonymous]

Hi

I am trying to get the below formula in Power BI. Can you pls help?

Table is:

SKU	L	W	H
A	0.25	0.15	0.1
B	0.2	0.25	0.3
C	0.3	0.35	0.4

= If(Largest of (SKU LWH) < 0.2 & 2nd Largest of (SKU LWH) < 0.1 & 3rd Largest of (Tote LWH) < 0.3, “X”,

If(Largest of (SKU LWH) < 0.2 & 2nd Largest of (SKU LWH) < 0.1 & 3rd Largest of (Tote LWH) < 0.3, “Y”, "Z”))

[VahidDM]

Hi @Anonymous 

What are those (SKU LWH) and  (Tote LWH)? Can you add more details?

Appreciate your Kudos!!

[Anonymous]

In the table, SKU (A,B & C) are the items with dimensions L,W & H.

I want to find out if SKU "A" with dmensions "0.25 x 0.15 x 0.1" can fit into a box with dimensions 0.3 x 0.2 x 0.1.

So in the formula, I am trying to pick the largest side of the SKU (L,W & H) and check if its smaller than the largest side of box which is 0.3. Similarly, next I am checking if the 2nd largest side of the SKU is smaller than 2nd largets side of the box which is 0.2 and finally similar calculation for the 3rd side of the SKU. There was a small correction in the formual which I have done. Pls refer to the below.

If(Largest of (SKU LWH) < 0.3 & 2nd Largest of (SKU LWH) < 0.2 & 3rd Largest of (SKU LWH) < 0.1, “X”,

If(Largest of (SKU LWH) < 0.1 & 2nd Largest of (SKU LWH) < 0.2 & 3rd Largest of (SKU LWH) < 0.1, “Y”, "Z”))

[v-rzhou-msft · Accepted solution]

Hi @Anonymous

It is better for you to calculate in a column instead of a row in Power BI. I suggest you to transform your table with unpivot function in Power Query Editor.

For reference: Pivot and Unpivot with Power BI

Then your table looks like as below.

Rank is a calculated column.

Rank of Attribute Per SKU = RANKX(FILTER('Unpivot Table','Unpivot Table'[SKU] = EARLIER('Unpivot Table'[SKU])),'Unpivot Table'[Value],,DESC,Dense)

Then create a measure to check wheter your SKU can fit the box in size of 0.3*0.2*0.1. I use the logic that Largest<0.3, 2nd Largest <0.2 and 3rd Largest <0.1 can fit and others can not fit.

Fit 0.3*0.2*0.1 or Not =
VAR _Largest =
    CALCULATE (
        SUM ( 'Unpivot Table'[Value] ),
        FILTER (
            ALL ( 'Unpivot Table' ),
            'Unpivot Table'[SKU] = MAX ( 'Unpivot Table'[SKU] )
                && 'Unpivot Table'[Rank of Attribute Per SKU] = 1
        )
    )
VAR _2ndLargest =
    CALCULATE (
        SUM ( 'Unpivot Table'[Value] ),
        FILTER (
            ALL ( 'Unpivot Table' ),
            'Unpivot Table'[SKU] = MAX ( 'Unpivot Table'[SKU] )
                && 'Unpivot Table'[Rank of Attribute Per SKU] = 2
        )
    )
VAR _3rdLargest =
    CALCULATE (
        SUM ( 'Unpivot Table'[Value] ),
        FILTER (
            ALL ( 'Unpivot Table' ),
            'Unpivot Table'[SKU] = MAX ( 'Unpivot Table'[SKU] )
                && 'Unpivot Table'[Rank of Attribute Per SKU] = 3
        )
    )
RETURN
    IF ( _Largest < 0.3 && _2ndLargest < 0.2 && _3rdLargest < 0.1, "Y", "N" )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.