Forum Discussion

dustdaniel's avatar
dustdaniel
Icon for Helper II rankHelper II
1 year ago
Solved

Conditional formatting less or grather than average

Hi everyone,   I have a Matrix with average handle time for a list of items. I need a measure to turn green when the average close time of the Item is below the total Average, red if it's greather ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi dustdaniel 

    Please try the following dax:

    Avg Close Time = 
VAR _a = CALCULATE(AVERAGE(Contrato[TimeCloseN]), USERELATIONSHIP(DateTable[Date], Contrato[FechaRetorno]))
VAR _Hours = HOUR(_a)
VAR _Minutes = MINUTE(_a)
VAR _Seconds = SECOND(_a)
RETURN

IF(NOT(ISBLANK(_a)), TIME(_Hours, _Minutes, _Seconds))

     

     

    Card Color Close Time Matrix = 
VAR ItemCloseTime = [Avg Close Time]
VAR _Year = VALUE(SELECTEDVALUE(DateTable[Calendar Year]))
VAR OverallCT = CALCULATE([Avg Close Time],ALLEXCEPT(DateTable,'DateTable'[Calendar Year]))
RETURN
SWITCH(
    TRUE(),
    ItemCloseTime < OverallCT, "#5FBEA5",
    ItemCloseTime > OverallCT, "#F55564",
    ItemCloseTime = OverallCT, "#F1C232"
)

     

     

    Result:

     

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dharmendars007's avatar
dharmendars007
Icon for Memorable Member rankMemorable Member
1 year ago

Hello dustdaniel , 

 

Please follow the below steps t achve the desired result.

 

1. Create a Measure for Conditional Formatting: This measure will return numerical values that correspond to the different colors:

 

Close Time Status =
VAR ItemCloseTime = AVERAGE('Table'[Close Time])
VAR OverallAvg = [Overall Avg Close Time] -- its a average measure which you have to create for close time
RETURN
SWITCH(TRUE(),
 ItemCloseTime < OverallAvg, "#008000", -- Green
ItemCloseTime > OverallAvg, "#FF0000", -- Red
ItemCloseTime = OverallAvg, "#FFFF00" -- Yellow)

 

Follow the below steps to apply the conditional formatting.

 

1. In Power BI Desktop, go to your Matrix visual.

2.Click on the drop-down for Close Time and select Conditional Formatting → Background Color.

3.Choose Field Value as the format style.

4.Use the Close Time Color measure as the color field.

 

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

 

 

 

 

 

  • dustdaniel's avatar
    dustdaniel
    Icon for Helper II rankHelper II
    1 year ago

    Thank you

     

    I didn´t explained the entired scenario, I was able to create the measure but this has to perform according the Year in the column. This is what I have:

    Card Color Close Time Matrix = 
VAR ItemCloseTime = [Avg Close Time]
VAR OverallCT = CALCULATE([Avg Close Time], ALLSELECTED())
RETURN
SWITCH(
    TRUE(),
    ItemCloseTime < OverallCT, "#5FBEA5",
    ItemCloseTime > OverallCT, "#F55564",
    ItemCloseTime = OverallCT, "#F1C232"
)

    This is the result 

     

    Since we are removing filters, the Overall avg is considering both years 2023 and 2024. The expected result should compare each item with the overall of the same year.

     

    Thanks for the support

    PD: the card of the top left corner is intentionally filtering 2024 for visual porpuses.