Forum Discussion
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 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
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.
11 Replies
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.
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
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.
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.
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.
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.
