Find lowest number in a row

[sudhakar111]

Hello Everyone,

I am trying to create a report baed on the attached screenshot.

Here the lowest number has to be highlighted in green, for each row.

Tried converting the last 4 product columns using transpose.If i iuse transpose, i am not able to show the table format.

I want to retain the table format with all the columns.

[v-easonf-msft · Accepted solution]

Hi , @sudhakar111 

To get the  lowest number in the row, I would suggest you to create  a custom column "Minimum" in PowerQuery Editor .

=List.Min({[Product1], [Product2], [Product3],[Product4]})

Conditional formatting is applied for column field  not row value, so you  need to create measure for each column:
Example: Product1 

Measure Product1_conditional_formatting =
VAR Product1 =
    MAX ( 'Table'[Product1] )
VAR Minimum =
    MAX ( 'Table'[Minimum] )
RETURN
    IF ( Product1 = Minimum, 1, 0 )

Then apply them to corresponding fields as below:

The result will show as below:

Please check the attached file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sumanth_23]

hi @sudhakar111, Do take a look at the below article for details on how you can implement conditional formatting at row level. 

https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Rows/td-p/550047

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[sudhakar111]

Thanks Sumanth. For your response. I want to find the lowest number in the row first, before doing the color format.The link yoy gave has the color formatting.

[v-easonf-msft · Accepted solution]

Hi , @sudhakar111 

To get the  lowest number in the row, I would suggest you to create  a custom column "Minimum" in PowerQuery Editor .

=List.Min({[Product1], [Product2], [Product3],[Product4]})

Conditional formatting is applied for column field  not row value, so you  need to create measure for each column:
Example: Product1 

Measure Product1_conditional_formatting =
VAR Product1 =
    MAX ( 'Table'[Product1] )
VAR Minimum =
    MAX ( 'Table'[Minimum] )
RETURN
    IF ( Product1 = Minimum, 1, 0 )

Then apply them to corresponding fields as below:

The result will show as below:

Please check the attached file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sudhakar111]

Thanks a lot @v-easonf-msft  , that worked perfectly. I would aslo like to know how to find the lowest value in a row, with the same example when we transpose the Product columns into a single column. Please find the images for source and output.

In the Output i want to highlight the lowest number.

[Sumanth_23]

hi @sudhakar111  - you will have to create a rank column and then implement the conditional formatting to achieve this. 

1. Below is the DAX that was used to create the Rank column - 

Rank by Product =

IF (

HASONEVALUE ( 'Table'[PRODUCT] ),

RANKX ( ALLSELECTED ( 'Table'[Sales Rep] ), ( [Value] ), ,ASC, Skip )

)

2. This should create a rabk for each Sales Rep / Product combination - something similar to the below screenshot 

You can then apply conditional formatting for all lines where Rank = 1

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[sudhakar111]

Thanks @Sumanth_23 , the rank function does not show any values when i included it in the matrix.

[Sumanth_23]

hi @sudhakar111 - Can you try adding a calculated column to the table to calculate the rank - this would also exclude the BLANKs

1. Calculated calculation column

Rank_Prod (ex blanks) =

VAR _Rnk =

RANKX (

FILTER (

'Rank-Lowest',

'Rank-Lowest'[Product] = EARLIER ( 'Rank-Lowest'[Product] )

&& 'Rank-Lowest'[Value] <> BLANK ()

),

'Rank-Lowest'[Value],

,

ASC,

DENSE

)

RETURN

IF ( 'Rank-Lowest'[Value] = BLANK (), BLANK (), _Rnk )

2. Use the calculated column to apply conditional formatting using the calculated column 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[sudhakar111]

Thanks a lot Sumanth, I tried the formula. It shows perfectly the product with the lowest values in a table. But my requirement is to show in a matrix. When i converted the table to matrix it is not working. I want to highlight the lowest value for each row, comparing multiple products,for each date and sales rep.

[Sumanth_23]

hi @sudhakar111 - That is cause the conditional formatting is still happening based on the rank assigned based on the values among each product. 

If you want to conditionally format using a rank for value among wach day then your rank column should be: 

Rank_Date (ex blanks) =

VAR _Rnk =

RANKX (

FILTER (

'Rank-Lowest',

'Rank-Lowest'[Date] = EARLIER ( 'Rank-Lowest'[Date] )

&& 'Rank-Lowest'[Value] <> BLANK ()

),

'Rank-Lowest'[Value],

,

ASC,

DENSE

)

RETURN

IF ( 'Rank-Lowest'[Value] = BLANK (), BLANK (), _Rnk )

And then use Rank_Date (ex blanks) measure to conditionally format your matrix. 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[sudhakar111]

Thanks Sumanth, The formula works if the user has only one date. Tried with multiple dates for some users. For each date it is showing only once. if the date repeats it is not showing anything. Dates 12th,13th and 14th repeated twice and the formula worked for one row.

[Sumanth_23]

@sudhakar111 - The rank was created based on date based on your previous comment; if you require to create the rank based on Sales Rep + Date then you can follow the below steps:

1. Create a key column 

SR-Date = 'Rank-Lowest'[Sales Rep] & "-" & 'Rank-Lowest'[Date]

2. Create rank using the key column "SR-Date"

Rank_SR-Date (ex blanks) =

VAR _Rnk =

RANKX (

FILTER (

'Rank-Lowest',

'Rank-Lowest'[SR-Date] = EARLIER ( 'Rank-Lowest'[SR-Date] )

&& 'Rank-Lowest'[Value] <> BLANK ()

),

'Rank-Lowest'[Value],

,

ASC,

DENSE

)

RETURN

IF ( 'Rank-Lowest'[Value] = BLANK (), BLANK (), _Rnk )

3. Use the new created column Rank_SR-Date (ex blanks) for conditional formatting

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[sudhakar111]

Thank you Sumanth, The formula is working fine with the sample data. I applied the formula to some real data. But it is not working.

I uploaded the sample PBIX to drop box. Here there are 2 tables Master and Detail,which are linked by Product. Master table has Product Group column which is used as filter for data in the matrix visual. Can you please check the file.

https://www.dropbox.com/s/78odnqb5slor3ly/Sample.pbix?dl=0

[Sumanth_23 · Accepted solution]

hi @sudhakar111 - If you need to provide an option to user to filter by Group then you need to include that in the "Key" (SR-Date) column. 

But what is the requirement? will the users be able to selected multiple groups from the slicer and you want to see the minimum among all selected groups or you want to see the minimum per group? 

If you use the calculated column logic which I have shared then it will give you the lowest value per group. 

https://drive.google.com/file/d/1PmzMW1HcoJZOMnN0qOM8-1Lirs3T8GMv/view?usp=sharing

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[sudhakar111]

Thanks Sumanth, This is the output i want. Based on the group selection the lowest number in the row should be higllighted.

Thanks a lot for your help.

[Sumanth_23]

hi @sudhakar111 - Happy to help! Do share your kudos 👍 for the solution provided. Thanks!