Highlighting min and max value in a table

[Anonymous]

Hi there, I tried to use conditional formatting to display the min and max values of a measure in a table. Like the one in table below which highlihghts value in green for max and min in red in a row.

This is what I got so far with Internal hire total being a measure.

FormatMeasure =

VAR SelectedTable = ALLSELECTED('FACT_Movement')

VAR SelectedTotal = CALCULATE(COUNT('FACT_Movement'[Employee ID]))

VAR MaxCount = CALCULATE(MAXX(VALUES('FACT_Movement'[Type of Move]),[Internal Hire total]),ALL('FACT_Movement'[Type of Move]))

VAR MinCount = CALCULATE(MINX(VALUES('FACT_Movement'[Type of Move]),[Internal Hire total]),ALL('FACT_Movement'[Type of Move]))

VAR MovementTotal = [Internal Hire total]

RETURN

SWITCH (

TRUE(),

MovementTotal= MaxCount, "GREEN",

MovementTotal=MinCount,"Red" )

[amitchandak]

@Anonymous , Try one of the two

FormatMeasure =
VAR SelectedTable = ALLSELECTED('FACT_Movement')
VAR SelectedTotal = CALCULATE(COUNT('FACT_Movement'[Employee ID]))
VAR MaxCount = CALCULATE(MAXX(VALUES('FACT_Movement'[Type of Move]),[Internal Hire total]),ALLEXCEPT('FACT_Movement'[Type of Move]))
VAR MinCount = CALCULATE(MINX(VALUES('FACT_Movement'[Type of Move]),[Internal Hire total]),ALLEXCEPT('FACT_Movement'[Type of Move]))
VAR MovementTotal = [Internal Hire total]
RETURN
SWITCH (
TRUE(),
MovementTotal= MaxCount, "GREEN",
MovementTotal=MinCount,"Red" )

or

FormatMeasure =
VAR SelectedTable = ALLSELECTED('FACT_Movement')
VAR SelectedTotal = CALCULATE(COUNT('FACT_Movement'[Employee ID]))
VAR MaxCount = CALCULATE(MAXX(VALUES('FACT_Movement'[Type of Move]),[Internal Hire total]),ALLEXCEPT('FACT_Movement'[GENDER]))
VAR MinCount = CALCULATE(MINX(VALUES('FACT_Movement'[Type of Move]),[Internal Hire total]),ALLEXCEPT('FACT_Movement'[GENDER]))
VAR MovementTotal = [Internal Hire total]
RETURN
SWITCH (
TRUE(),
MovementTotal= MaxCount, "GREEN",
MovementTotal=MinCount,"Red" )

[Anonymous · Accepted solution]

Hi @Anonymous use the below measure and use it inside the conditional formatting pane to get the results.

Measure = var rnk1=rankx(allselected(particulars),yourmeasure,,desc)

var rnk2 = rankx(allselected(particulars),yourmeasure,,asc)

return if(rnk1=1,"Green",if(rnk2=1,"Red"))

This will surely work, please mark this as solution if this works fine with you.

Thanks.