cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Regular Visitor

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors