Forum Discussion

tayriley88's avatar
tayriley88
Frequent Visitor
7 years ago
Solved

Conditional Formatting Top and Lowest Value in Matrix Visual

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
tayriley88
Frequent Visitor
7 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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