cancel
Showing results for
Did you mean: Frequent Visitor

## Determine Minimum and Maximum Value in Row in Matrix

Hi

I am relatively new to power bi

I have a dataset that looks as follows

 Country Supplier Value A Acme 10 A Illuminati 12 A Batman 14 B Acme 20 B Illuminati 15 B Batman 12 C Acme 20 C Illuminati 35 C Batman 22

I then create a matrix so the ouput is as follows

 Row Labels Acme Batman Illuminati A 10 14 12 B 20 12 15 C 20 22 35

I want to apply conditional formatting to highlight the lowest value in a row and the highest value in a row but it seems to apply on the whole matrix. How would I determin the lowest value per row if lets say I want to add on to the matrix. Any help will be much appreciated.

1 ACCEPTED SOLUTION  Community Champion

@fdkza

Hi,

You can use a MEASURE to do the conditional formatting

```Measure =
VAR mymin =
CALCULATE ( MIN ( Table1[Value] ), ALL ( Table1[Supplier ] ) )
VAR mymax =
CALCULATE ( MAX ( Table1[Value] ), ALL ( Table1[Supplier ] ) )
RETURN
SWITCH (
TRUE (),
SUM ( Table1[Value] ) = mymin, "#FF7F00",
SUM ( Table1[Value] ) = mymax, "#2AAAFF"
)
```

Now in the Conditional Formatting dialogue

Select as follows Regards
Zubair

9 REPLIES 9  Community Champion

@fdkza

Hi,

You can use a MEASURE to do the conditional formatting

```Measure =
VAR mymin =
CALCULATE ( MIN ( Table1[Value] ), ALL ( Table1[Supplier ] ) )
VAR mymax =
CALCULATE ( MAX ( Table1[Value] ), ALL ( Table1[Supplier ] ) )
RETURN
SWITCH (
TRUE (),
SUM ( Table1[Value] ) = mymin, "#FF7F00",
SUM ( Table1[Value] ) = mymax, "#2AAAFF"
)
```

Now in the Conditional Formatting dialogue

Select as follows Regards
Zubair  Community Champion

@fdkza

See attached file as well Regards
Zubair New Member

Thank you, this work perfect incase all my value >0

I am struggling to highlight min value of each row >0

This is desired output

 Area Price 1 Price 2 Price 3 A 0 12 15 B 20 40 0 Frequent Visitor Frequent Visitor

Just one last question 🙂 If i want to count the number of values where supplier is the cheapest or the most expensive how will i go about it.  Community Champion

@fdkza

What is your expected output with above data?

Regards
Zubair Frequent Visitor

Hi

I would like to display a seperate matrix or table layed out as follows

 Supplier Lowest Values Highest Values Total Values Acme 2 1 3 Batman 1 1 3 Illuminati 0 1 3  Community Champion

@fdkza

You can add these calculated columns

Please see revised file as well

```Highest =
VAR temp =
MAXX ( FILTER ( table1, [Country] = EARLIER ( Table1[Country] ) ), [Value] )
RETURN
IF ( [Value] = temp, 1 )
```
```Lowest =
VAR temp =
MINX ( FILTER ( table1, [Country] = EARLIER ( Table1[Country] ) ), [Value] )
RETURN
IF ( [Value] = temp, 1 )
``` Regards
Zubair  Post Partisan

In my case, the conditional format measure is not working while I am putting that measure in conditional format field value. Thanks,

Arvind Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  