Create a new calculated field (use the Analysis menu item then "Create Calculated Field" or right click in the Measures or Dimensions area of the Data Window). The code should be something like:
SUM(Revenue) / SUM(Units)
Field names are case sensitive and should be wrapped in brackets if they contain spaces -- e.g. [Revenue Amount]
In this case, you'll want to be sure to use an aggregate function (the SUM indicated above). If you used a row level function like:
Revenue / Units
Then you'd get the correct answer at the row level, but other levels of aggregation would likely be wrong.
Hey Josh, that was great help. But, I need some more help now.
So now I have another problem. I have product type as row heading and sum of revenue and a count of distinct customer name as another measure. How do I divide that revenue by that distinct customer name count.
Revenue Distinct Count of Adv Name
Product Type A 3000000 400
Product Type B 4000000 700
If I use the regular average it divide 300000 by 800 because the data has some advertiser name twice.
So how do I do this ?
Your help is greatly appreciated.
I think I got it. It's COUNTD, right ?
Yes, countd is count distinct.
You can also divide the sum of one measure by the value of another measure in the same row of data. This uses the Advanced Table Calculation features.
Firstly you create a calculated field for the "RowTotal" e.g.
TOTAL(SUM([impressions_supplied_count])) - Note this is an Advanced Table Calculation that summarizes values from brand_name, delivery_date. The calculation restarts at zero for every brand_name
Click on Default Table Calculation
Select "Advanced" from Compute Using
Select the values you have in your Rows (Here I'm using brand_name and delivery_date) then click OK.
Select "Deepest" for At the Level:
Restarting Every: (select the value for the column on the right - for me that was delivery_date)
Compute totals across all pages - Checked.
You can then use this in a calculation of another field - e.g. Percent of Total
SUM([impressions_supplied_count])/([TOTAL Impressions Supplied])
Drag your Percent of Total field up to the Measure Values, then right click on it and select Edit Table Calculation
Compute using Specific Dimensions, select the column and rows, At the level = column_name, Restarting Every = rightmost column. Automatic sort should be OK.