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

Accepted Solutions Super User

## Re: Determine Minimum and Maximum Value in Row in Matrix

@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 7 REPLIES 7 Super User

## Re: Determine Minimum and Maximum Value in Row in Matrix

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

## Re: Determine Minimum and Maximum Value in Row in Matrix

@fdkza

See attached file as well fdkza Frequent Visitor

## Re: Determine Minimum and Maximum Value in Row in Matrix

fdkza Frequent Visitor

## Re: Determine Minimum and Maximum Value in Row in Matrix

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

## Re: Determine Minimum and Maximum Value in Row in Matrix

@fdkza

What is your expected output with above data?

fdkza Frequent Visitor

## Re: Determine Minimum and Maximum Value in Row in Matrix

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

## Re: Determine Minimum and Maximum Value in Row in Matrix

@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 )
``` 