4 Replies Latest reply on Jul 7, 2016 4:24 PM by swaroop.gantela

# Workaround to Sum(Avg[Measure]) Aggregation Problem

I need to find a way to sum up a set of numbers that are averages. I have a data set that contains Advertisers, their ads, how many clicks each ad got, and (on a separate sheet) each advertiser and their monthly budget. I have a sheet that shows 2 lines, one of their clicks per month and the  other showing the average budget for all advertisers for each month. I had to set the budget to be an average because when I joined the data it duplicated the monthly budget down every instance of an ad within each advertiser. Since the budget for an advertiser won't change within the month I averaged the budget and select which advertiser I want to see on the side filter. This gives me their actual budget per month (instead of a huge sum because of the duplication).

This means that if I have all advertisers selected in the filter that it averages budget per month by the number of advertisers. My PM wants to see the total budget every month when I have multiple or all advertisers selected, basically a total of the budget of each vendor. I tried to do a quick SUM(AVG[Budget]) but I can't use SUM on an aggregated measure. I've been looking through forums trying to find a workaround but so far no luck. If anyone can help I would greatly appreciate it! Please see screenshot below and attached sample dataset.

• ###### 1. Re: Workaround to Sum(Avg[Measure]) Aggregation Problem

Sierra,

Please see if the attached could be a first step for you.

I think the way to work around it is to use Level of Detail calculations.

I think you can get at the true budget by counting the number of copies you have of them.

So first [CountCopies]:

{ FIXED [Month and year],[Advertiser]:COUNT([Number of Records])}

Then the [TrueBudget] is:

SUM([Budget])/SUM([CountCopies])

With that, you can fix the true budget for a Month and Advertiser with:

{ FIXED [Month],[Advertiser]:[TrueBudget]}

I think now when you add the FixedBudget to your graph, it will sum when multiple

Advertisers are selected (red line).

I'll think about whether there may be other ways to set it up.

1 of 1 people found this helpful
• ###### 2. Re: Workaround to Sum(Avg[Measure]) Aggregation Problem

Thank you so much for helping me with this! I played around with your example data and compared it to the real data and it is correct! However, when I try to implement it with the real data I'm not getting the same results. I'm getting much lower amounts for FixedBudget and if I select only one vendor at a time then I don't get any results. I've written out my calculated fields below and added a screenshot of what I'm seeing with all Advertisers selected, please  let me know if you see anything that could be causing the issue. Thank you!

[CountCopies]:

{ FIXED [Month and year],[Advertiser]:COUNT([Number of Records])}

[TrueBudget]:

SUM([Budget])/SUM([CountCopies])

[FixedBudget]:

{ FIXED [Month and year],[Advertiser]:[TrueBudget]}

• ###### 3. Re: Workaround to Sum(Avg[Measure]) Aggregation Problem

Nevermind on needing more help! I realized there were some input errors for the dates in the data set that I needed to update. I also adjusted the joining method and now I'm getting the correct numbers! Thanks for your help with this!!

• ###### 4. Re: Workaround to Sum(Avg[Measure]) Aggregation Problem

Sierra,

Glad it worked.

All the best.