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

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.

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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