Forum Discussion

dustdaniel's avatar
dustdaniel
Helper 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.

hnguy71's avatar
hnguy71
Super User
1 year ago

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.