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

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

 

cformt.png

 

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

 

cformt.png

 

Super User
Super User

Re: Determine Minimum and Maximum Value in Row in Matrix

@fdkza

 

See attached file as well

 

cformt1.png

fdkza Frequent Visitor
Frequent Visitor

Re: Determine Minimum and Maximum Value in Row in Matrix

 

fdkza Frequent Visitor
Frequent Visitor

Re: Determine Minimum and Maximum Value in Row in Matrix

@Zubair_Muhammad

 

Just one last question Smiley Happy 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
Super User

Re: Determine Minimum and Maximum Value in Row in Matrix

@fdkza

 

What is your expected output with above data?

fdkza Frequent Visitor
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 

 

SupplierLowest Values Highest Values Total Values 
Acme 213
Batman113
Illuminati 013
Super User
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 )

dfc.png