Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.