6 Replies Latest reply on Feb 7, 2019 11:07 AM by Mitch Posluns Branched to a new discussion.

# How to Divide the sum of one measure by another measure

I have product type in row, and sum of its corresponding revenue in measure and also sum of units. Now I need to divide the two columns to get the average revenue per unit. Looks like a pretty basic calculation, but can't figure out a way to do it, new to this software.

• ###### 1. Re: How to Divide the sum of one measure by another measure

Karan,

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.

Regards,

Joshua

• ###### 2. Re: How to Divide the sum of one measure by another measure

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 ?

• ###### 3. Re: How to Divide the sum of one measure by another measure

I think I got it. It's COUNTD, right ?

• ###### 4. Re: How to Divide the sum of one measure by another measure

Yes, countd is count distinct.

• ###### 5. Re: How to Divide the sum of one measure by another measure

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

1 of 1 people found this helpful
• ###### 6. Re: How to Divide the sum of one measure by another measure

Amazing thank you!