color based on the average in pivot table

[#1]

Hello everyone, I need to highlight for each individual row values that rise or lower than 30% from the total of the line media.
The values that are shown in the table, the ratio of two fields:

(Sum ({<metername = {'EAT'}, assetnum = {'IT00 *'}, indicatoreEnergia = {'1'}>} [measurementvalue])
/
(Sum ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} reflui_IN_OUT) -SUM ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} bypass)))

can someone help me?
thank you !

[sunny_talwar]

Line media? is that Collocazione? May be this

If(

fabs(

(Sum ({<metername = {'EAT'}, assetnum = {'IT00 *'}, indicatoreEnergia = {'1'}>} [measurementvalue])

/

(Sum ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} reflui_IN_OUT) -SUM ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} bypass))),

/

(Sum(TOTAL <Collocazione> {<metername = {'EAT'}, assetnum = {'IT00 *'}, indicatoreEnergia = {'1'}>} [measurementvalue])

/

(Sum(TOTAL <Collocazione> {<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} reflui_IN_OUT) -SUM ({<TransType = {'reflui'}, indicatoreEnergia = {'1'}>} bypass))) -1) >=0.30, Red(), Green())

[#3]

there is an error...

attaching  the qwv for example

[sunny_talwar]

I think it would be helpful if you can point out which cells are you hoping to see colored here?

[#5]

for example:
the first line (05F09D08) the total is 0.66 then the range of values included in the 30% above and below are 0.86 and 0.46. I would be interested to highlight the values that are not included in the range as in the attached image.
So for all rows.
thank you so much

[sunny_talwar · Accepted solution]

Like this?

Background color expression:

=If(Kwh_Mc <= (Sum(TOTAL <location> {<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])

/

(Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))) * 0.70

or

Kwh_Mc >= (Sum(TOTAL <location> {<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])

/

(Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-Sum(TOTAL <location> {<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))) * 1.30, LightRed())

[stabben23]

Try this in Background color on Expression:

if(sum(aggr((sum({<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])
/
(sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))),Year,location,Month))
> sum(total <location> aggr((sum({<metername = {'EAT'},assetnum={'IT00*'},indicatoreEnergia ={'1'}>} [measurementvalue])
/
(sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}reflui_IN_OUT)-sum({<TransType={'reflui'},indicatoreEnergia ={'1'}>}bypass))),location)),red(),green())

EDIT!! OK, I'll miss the 30% limit up and down

[#8]

thank you so much
very nice