Forum Discussion

AgoZ_KH's avatar
AgoZ_KH
Resolver I
7 years ago
Solved

Color a table conditionally using SWITCH command

I'm tryng to use SWITCH command for a Table conditional formatting.

 

The syntax command -that function correctly with table- is:

ColorDL = SWITCH(TRUE();AVERAGE(datatest[DWLMbps]) <= 40;"#ff0000")

 

I want to use the above formula with varable values that I have definited with:
AlertDL = AVERAGE(datatest[DWLMbps]) / 2

 

So the formula I want to use is:

ColorDL = SWITCH(TRUE();AVERAGE(datatest[DWLMbps]) <= AlertDL ;"#ff0000")

but if I change the formula with the variable nothing happen!!

 

Has anyone idea how to solve this issue??

 

Thanks in advance

 

Ago

 

 

Need Help
  • AgoZ_KH's avatar
    AgoZ_KH
    7 years ago

    I guess to have found my gross error! For my pourpose I must have to find as constant the average of average in a specific new calculation!!! Now the formula is right!

    Thanks and sorry!!

    Ago

     

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi AgoZ_KH ,

    I have a little confused about your scenario.

    Do you use the formula below and it does not work? 

    ColorDL =
VAR AlertDL =
    AVERAGE ( datatest[DWLMbps] ) / 2
RETURN
    SWITCH ( TRUE (); AVERAGE ( datatest[DWLMbps] ) <= AlertDL; "#ff0000" )

    If it is convenient, could you share your data sample and your desired output so that we could help further on it?

    Best Regards,

    Cherry

     

     

    • AgoZ_KH's avatar
      AgoZ_KH
      Resolver I
      7 years ago

      Hi v-piga-msft  !

      thanks for your reply!

      I try to explain better my issue.

       

      Suppose you have a table like this:

      CityproviderAVG DLAVG UL
      NiscemiWe-Link srl8,3614690967,863967693
      CataniaSielte S.p.A.35,735971988,044349001
      TurinIT.Gate S.p.A.43,211819667,76261368
      AvellinoWirLab56,386936577,979770532
      FerraraSTEL s.r.l.61,194724717,936191003
      CampagnaFarecom6,5782434967,961401015
      Ascoli Picenoip telecom srl63,513297287,9339651
      LavelloFlashLine63,598298137,957919972
      MolfettaWebFi S.r.l.63,937524918,011353872
      SalernoWirLab64,090264127,989395117
      FrancofonteMicro Servizi64,642352657,910004308
      PerdifumoSAMTEL NETWORK SRL65,23613927,874640341
      LecceWicitY5,249762887,852466136
      SalernoPositivo65,401636737,900549613
      FolignoConnesi s.p.a.65,426419347,968541377
      SerranoFowhe Broadband65,569781967,91225221
      TivoliAirMaxItalia65,615356127,918500548
      BarlettaNova Networks srl7,6513180517,884948795
      CampobassoDimensione Srl65,662152037,910134884

       

      Suppose the Average of column AVG DL is 49,31, too.

      I want to color all the cells in AVG DL column that are < to 49,31/2 = 24,65 using the formula you have described below in your gentle reply.

       

      I'd like to understand why the formula works when I use a scalar value (i.e. 24) and not when I use the variable (AlertDL)!

      Thank in advance for your help!!

      Ago

      • AgoZ_KH's avatar
        AgoZ_KH
        Resolver I
        7 years ago

        I guess to have found my gross error! For my pourpose I must have to find as constant the average of average in a specific new calculation!!! Now the formula is right!

        Thanks and sorry!!

        Ago