Determine Minimum and Maximum Value in Row in Matrix

[fdkza]

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. 

[Zubair_Muhammad · Accepted solution]

@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

[Zubair_Muhammad]

@fdkza

See attached file as well

[Anonymous]

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 1	Price 2	Price 3
A	0	12	15
B	20	40	0

[fdkza]

@Zubair_Muhammad thank you very much. This helped! 

You are a legend! 

[fdkza]

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

[Zubair_Muhammad]

@fdkza

What is your expected output with above data?

[fdkza]

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

[Zubair_Muhammad]

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

[arvindyadav]

Hi @Zubair_Muhammad ,

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

Thanks,

Arvind