Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Subtracting row value from total column avg in straight table

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.

13 Replies

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))))))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor III
Author
 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.

Are you not seeing this after using my expression?

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