cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nageshkiran
New Member

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

1 ACCEPTED SOLUTION

Hi @nageshkiran

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.

1.png

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.

2.png

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.

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

Hi @nageshkiran 

 

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

 

Appreciate your Kudos!!

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

Hi @nageshkiran

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.

1.png

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.

2.png

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors