cancel
Showing results for
Search instead 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

Please try my custom visuals
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

Please try my custom visuals
Community Champion

@fdkza

See attached file as well

Regards
Zubair

Please try my custom visuals
New Member

Hi Zubair_Muhammad,

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

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

Could you please advise how to do it. Thank you somuch

This is desired output

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

Frequent Visitor

@Zubair_Muhammad

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

Please try my custom visuals
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

Please try my custom visuals
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

## Helpful resources

Announcements

#### Check it Out!

Click here to learn more about the August 2022 updates!

#### The Power BI Community Show

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

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors