5 Replies Latest reply on Nov 15, 2016 11:02 PM by Brad Veitch Branched to a new discussion.

    How to Divide the sum of one measure by another measure

    Karan Sood

      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
          Joshua Milligan

          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
            Karan Sood

            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.

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

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

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

                Yes, countd is count distinct.

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

                  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.