Percentage of Total Spend in Pivot, Table, or Content View

stilley · May 20, 2011 11:15AM

I have a question about how to create a formula for that will calculate the Percentage of Spend in TV for each competitor. I could do this in a pivot view, table view or content view. Basically it needs to look like this:
Company TV Spend % Total Budget
Bank Of America Corp $154,614 61%
Citigroup Inc $40,111 51%
Goldman Sachs Group Inc 0%
JP Morgan Chase & Co $164,152 52%
Morgan Stanley $3 0%

And I would like it if it would change depending on the time period that is selected. Is it possible to use a formula to calculate the percentage of TV Spend from the whole spend for each competitor?

steve · May 21, 2011 1:47AM

I'm assuming you have the fields Competitor, Media Group and Spend.

Add a formula field "Total spend per competitor":
SUBSET_SUM([Spend], SUBSET([Competitor]))

Add a formula field "Total spend per competitor+media group":
SUBSET_SUM([Spend], SUBSET2([Competitor], [Media Group]))

Add a formula field "% Total Budget"
[Total spend per competitor+media group] / [Total spend per competitor]

Configure last field as a percent with 0 decimal places.
In the table view, open Aggregate, and:
- tick Competitor and Media Group
- change the 3 formula functions to "Singleton value".
Hide the first two formula fields in the Field picker for the table view.

Now use the filters to show only the "TV" media group.

You can do this all visually in the Bar view without formulas:
Split: Competitor
Stack: Media Group
Measure: Spend
100% bars

stilley · May 23, 2011 10:30AM

Hi Steve,

Thanks for replying! Unfortunately this formula doesn't calculate the exact percentage...sometimes it is off by as much as 20%. It works though if I don't filter my time periods. Is there a way to factor this into the formula? I would really like to use the table view!

Thanks for your help!

mohamed · June 6, 2011 3:23PM

If you need to show competitor shares for each competitor and including the time period. You need to update the formulas to be "SUBSET_SUM([Spend], SUBSET2([Competitor],[Time Period]))" for the "Total spend per competitor+media group" and aggregate by "Competitor" and "Time Period".

Welcome!

Categories

Ideas Parade