Color a table conditionally using SWITCH command

[AgoZ_KH]

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

[v-piga-msft]

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]

Hi @v-piga-msft  !

thanks for your reply!

I try to explain better my issue.

Suppose you have a table like this:

City	provider	AVG DL	AVG UL
Niscemi	We-Link srl	8,361469096	7,863967693
Catania	Sielte S.p.A.	35,73597198	8,044349001
Turin	IT.Gate S.p.A.	43,21181966	7,76261368
Avellino	WirLab	56,38693657	7,979770532
Ferrara	STEL s.r.l.	61,19472471	7,936191003
Campagna	Farecom	6,578243496	7,961401015
Ascoli Piceno	ip telecom srl	63,51329728	7,9339651
Lavello	FlashLine	63,59829813	7,957919972
Molfetta	WebFi S.r.l.	63,93752491	8,011353872
Salerno	WirLab	64,09026412	7,989395117
Francofonte	Micro Servizi	64,64235265	7,910004308
Perdifumo	SAMTEL NETWORK SRL	65,2361392	7,874640341
Lecce	WicitY	5,24976288	7,852466136
Salerno	Positivo	65,40163673	7,900549613
Foligno	Connesi s.p.a.	65,42641934	7,968541377
Serrano	Fowhe Broadband	65,56978196	7,91225221
Tivoli	AirMaxItalia	65,61535612	7,918500548
Barletta	Nova Networks srl	7,651318051	7,884948795
Campobasso	Dimensione Srl	65,66215203	7,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 · Accepted solution]

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