Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Largest values from a row

Hi

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

 

Table is:

 

SKULWH
A0.250.150.1
B0.20.250.3
C0.30.350.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”))

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

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

     

    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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”))

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.