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

 

CountrySupplier Value 
AAcme10
AIlluminati12
ABatman14
BAcme 20
BIlluminati 15
BBatman 12
CAcme 20
CIlluminati 35
CBatman 22

 

 

I then create a matrix so the ouput is as follows 

 

Row LabelsAcmeBatmanIlluminati
A101412
B201215
C202235

 

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
Zubair_Muhammad
Community Champion
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

 

cformt.png

 


Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
Zubair_Muhammad
Community Champion
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

 

cformt.png

 


Regards
Zubair

Please try my custom visuals

@fdkza

 

See attached file as well

 

cformt1.png


Regards
Zubair

Please try my custom visuals

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 1Price 2Price 3
A01215
B20400

 

fdkza
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. 

@fdkza

 

What is your expected output with above data?


Regards
Zubair

Please try my custom visuals

Hi 

 

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

 

SupplierLowest Values Highest Values Total Values 
Acme 213
Batman113
Illuminati 013

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

dfc.png


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad ,

 

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

Thanks,

Arvind

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power Platform Conf 2022 768x460.jpg

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