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 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      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's avatar
        Anonymous
        Not applicable
        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.