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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    dustdaniel
    Helper II
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        dustdaniel
        Helper II
        1 year ago

        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