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

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

1 ACCEPTED SOLUTION  Community Support

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 3  Super User

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

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

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. Announcements #### The Power BI Community Show

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

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th! Top Solution Authors
Top Kudoed Authors
Users online (1,395)