Conditional formatting less or grather than average

[dustdaniel]

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 and yellow in case is equal.

For example in 2024 Item2 is 07:20 should turn red since is grather than the overall average which is 06:40 and Item3 should be green since it is below 06:40.

Thanks for the help

[Anonymous]

Hi @dustdaniel 

Based on your needs, I have created the following table.

The average of the 2023 value is 4.25, and 2024 is 5.50.

Then you can create the following measure:

Measure = 
VAR _year = SELECTEDVALUE('Table'[Year])
VAR _avg = AVERAGEX(FILTER(ALL('Table'),'Table'[Year] = _year),'Table'[Value])
VAR _value = CALCULATE(SELECTEDVALUE('Table'[Value]),FILTER('Table','Table'[Year] = _year))
RETURN
SWITCH(
    TRUE(),
    _value < _avg, "#5FBEA5",
    _value > _avg, "#F55564",
    _value = _avg, "#F1C232"
)

Result:

Please change the formula I provided based on the columns in your existing table.

Best Regards,

Jayleny

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

[dustdaniel]

Thank You!

I'm having difficulties implementing this in my model since I have a Calendar Table separated from the values table.

Whould you be so kind to help me with that?

This is the measure that I'm using for the average in the matrix

Avg Close Time = 
VAR _a = CALCULATE(AVERAGE(Contrato[TimeCloseN]), USERELATIONSHIP(DateTable[Date], Contrato[FechaRetorno]))
RETURN
IF(NOT(ISBLANK(_a)), FORMAT(_a, "HH:MM:SS"))

and this is a my model:

FechaSalida is the Active relationship, all the other dates are not active, the one I need is the Return date (FechaRetorno)

I hope this is enough information.

[Anonymous]

Hi @dustdaniel 

According to the informa in your picture, pleas change the type of field "Contrato[FechaRetorno]" from text to number.

Best Regards,

Jayleny

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

[dustdaniel]

I was able to create a sample file of my data, I tried your recommendation about changing the type of field but it didn't work for me.

Would you be so kind and help me to fix it?

File link here 

Thanks in advance

[Anonymous]

Hi @dustdaniel 

In the file you provided, the calculated column in the image is causing an error, which is preventing the measure for conditional formatting from functioning correctly. However, I cannot find any related fields in your table. Please resolve this error and then provide your sample file.

Best Regards,

Jayleny

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

[dustdaniel]

Hi @Anonymous 

I just fixed the required column.

I think a new link is required now. Here it is.
Conditional formatting help 

Regards

HP

[Anonymous · Accepted solution]

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.

[dustdaniel]

Thank you very much Jay!

For sure it does as it was requested. I just didn't know it could affect other areas in my reports, for exampl if I try to put something different in rows different than a date, it won't perform. For example a list of offices.

is there a way to fix it?

Here is the sample updated Conditional Formatting help2 

[dharmendars007]

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]

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.

[hnguy71]

Hi @dustdaniel 

Seems pretty easy. You would need to create a new measure that retrieves the overall average time to close and then compare it with the current row visual context. Since don't see your measure we'll just have to eyeball or guess but the general idea is this:

ATTC.Condition = 
VAR _ATTC = CALCULATE([YOUR_ATTC_MEASURE], ALLSELECTED())
VAR _Close = [YOUR_CLOSE_TIME_MEASURE]
RETURN
IF(_Close > _ATTC, "#FF0000", "#008000")

Then on the cell element formatting, put your measure in there.