Subtracting row value from total column avg in straight table

[yohann24]

Hi, In the last column I am trying to populate values from SM% Column by subtracting the row value minus total avg i.e. 73.52-60.47 =, like wise for rest of the rows. can someone please help me on this.

SM% = SM$/Column(2)

** I have selected one Prod hier lvl0 here but we have similarly having 5 values under this lvl 0. Under each lvl 0 we have few lvl 3 values as shown here. so the calculation is driven by lvl0 & lvl3 & Pole. lvl 3s is under lvl0.

[Anil_Babu_Samineni]

What if we go like below for SM% vs BU Avg

If(POLE_ILT = 'ALL REGIONS', if(

IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} USD_NET_SALES)/1000000)=0,0,

((IF(POLE_ILT = 'ALL REGIONS', Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)},Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} USD_NET_SALES_MARGIN)/1000000))/

(IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} USD_NET_SALES)/1000000))))) - Avg(If(POLE_ILT = 'ALL REGIONS', if(

IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} TOTAL USD_NET_SALES)/1000000)=0,0,

((IF(POLE_ILT = 'ALL REGIONS', Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)},Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} TOTAL USD_NET_SALES_MARGIN)/1000000))/

(IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} TOTAL USD_NET_SALES)/1000000))))))

[yohann24]

SM%	SM% vs BU Avg
60.47%	27.11%
73.52%	16.44%	13.050473912730300%
18.68%	-38.40%	-54.840223894839200%
100.00%	42.92%	39.529940473609100%
100.00%	42.92%	26.479466560878800%
41.58%	-15.50%	22.896853587444400%
46.15%	-10.93%	-53.848847870958000%
62.43%	5.34%	-37.574783948372300%
41.41%	-15.68%	-0.171061216398277%

its appearing like this.. i have downloaded to an excel and compared with the new formula.

[Anil_Babu_Samineni]

Are you not seeing this after using my expression?

Highlight - Ask me if you want to remove the image from threat security.

[yohann24]

yes you are rt. I am not able to see these figures as shown in the pic shared by you rather the figures are something which i shared in my earlier response.