Conditional Formatting Top and Lowest Value in Matrix Visual

[tayriley88]

Hello,

I'm currently building a Matrix with Count of Open and Closed Tickets for a Manager in a Help Desk. Looking to highlight the top 1 value and lowest value in each column for a easier visual to see the top and lowest performers.

I created the following 2 measures in DAX and the top value is showing up formatted with the green background color, but the false statement on the IF statement will not format the lowest value. Any help would be much appreciated.

Measure #1:
Count of Opened Tickets = COUNTA('Opened By Tickets'[Ticket])

Measure#2

VAR RankingContext = VALUES(Contacts[Full_name])
VAR TopNumber = 1
RETURN
IF(
CALCULATE(
[Count of Opened Tickets],
TOPN(TopNumber,ALL(Contacts[Full_name]),[Count of Opened Tickets],DESC),RankingContext),"#5ff442",IF(CALCULATE([Count of Opened Tickets],
TOPN(TopNumber,ALL(Contacts[Full_name]),[Count of Opened Tickets],ASC),RankingContext),"#ff0000"))

Added a view of how the data looks in the Visual:

https://imgur.com/Bw0zX6u

[v-juanli-msft · Accepted solution]

Hi @tayriley88 

Since columns/measures added in the "Value" field of the matrix are sperate, you need to format color for each column/measure.

For example,to  format for Opened Tickets,

First create measures for Opened Tickets,

opened_tickets = COUNT('opened by tickets'[tickets])

rank_open = RANKX(ALL(contacts[full_name]),[opened_tickets],,DESC,Dense)

colored_opened =
VAR top_open =
    MINX ( ALL ( contacts[full_name] ), [rank_open] )
VAR low_open =
    MAXX ( ALL ( contacts[full_name] ), [rank_open] )
RETURN
    SWITCH ( [rank_open], top_open, 1, low_open, 0 )

Next, add conditional formatting for the "opened_tickets" field,

See how to add "Conditional formatting in tables/matrix"

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tayriley88]

Awesome thank you very much!

[v-juanli-msft]

Hi @tayriley88 

It may be easy to format with "conditional formatting" feature in Matrix as this article introduced.

You need to create a measure to perform this.

Before creating correct measures, please clear me:

How do "Contacts" table  and "Opened By Tickets" relate?

It seems you add [Full_name] from "Contacts" table and [Count of Opened Tickets] from "Opened By Tickets" table in the matrix visual.

Could you provide a screenshot of your matrix and which columns added into the "column"/"row"/"value" fields?

Best Regards

Maggie

[tayriley88]

Here is how the Rows and Values are added

Here is my relationship map. Contacts Table and Opened By Tickets  are linked by opened by from Opened By Tickets and Full_name on the Contacts Table.

[Anonymous]

In a matrix visual how to achieve this ?

I have this measure,

MinMax =

VAR Vals =

CALCULATETABLE(

ADDCOLUMNS (

SUMMARIZE ( Sheet1, Sheet1[Portal Error Reason],Sheet1[MonthRanking] ),

"@SalesAmt", [Portal Error]

),

ALLSELECTED ()

)

VAR MinValue = MINX ( Vals, [@SalesAmt] )

VAR MaxValue = MAXX ( Vals, [@SalesAmt] )

VAR CurrentValue = [Portal Error]

VAR Result =

SWITCH (

TRUE,

CurrentValue = MinValue, 1, -- 1 for MIN

CurrentValue = MaxValue, 2 -- 2 for MAX

)

RETURN

Result

This measure is being added as a condition for coloring

What is needed is to be able to color each column april'21 low and high, marcg'21 low and high and so on. How to modify this