cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyingod Frequent Visitor
Frequent Visitor

AdWords avg. position calculation

Hello,

 

Super newbie here... so this might be a really easy one.

 

Regardless of whether or not you know AdWords, to calculate Average position in Excel, one can just do the following formula:

 

= (Impressions * Avg. Position) / Impressions

 

Essential a weighted average.

 

However this won't do in Power BI. It returns errors, even when I create a Column in my table where the first calculation (Impressions * Avg. position) is done prior/separately to the division

 

Can anyone help? Am I not being specific enough? Super newbie so I've not much clue about M or DaX even though I have been able to make simple calculations work on both languages. I think I'm missing some key knowledge of aggregation?

 

Thank you

 

R

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: AdWords avg. position calculation

Perfect, I created a column using this formula:

 

 

WeightedAverage = ([Impressions] * [Avg. position]) / [Impressions]
 

I also created a measure like this:

 

mWeightedAverage = (SUM(Sheet2[Impressions]) * AVERAGE(Sheet2[Avg. position])) / SUM(Sheet2[Impressions])

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


7 REPLIES 7
Super User
Super User

Re: AdWords avg. position calculation

Seems like a straight-forward calculation, what kind of error are you seeing? Any chance you can post sample data?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


dyingod Frequent Visitor
Frequent Visitor

Re: AdWords avg. position calculation

Hey smoupre! Thanks for answering the call!

 

What sort of data do you need? Here is a screenshot of the table I am trying to plug this as a Mesure:

 

If you need anything else let me know! More than happy to supply! 

 

powerbi-1.PNG

Super User
Super User

Re: AdWords avg. position calculation

Can you post that in text so that I can copy and paste easily into an Enter Data query?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


dyingod Frequent Visitor
Frequent Visitor

Re: AdWords avg. position calculation

Hey!

 

Is this better? I exported some of the data from the table I'm using.

 

https://docs.google.com/spreadsheets/d/1msWrta5KOl1SsnC5sqIU2yOlBHDAlkr2zOWQ4TQLIvE/edit?usp=sharing

Super User
Super User

Re: AdWords avg. position calculation

Perfect, I created a column using this formula:

 

 

WeightedAverage = ([Impressions] * [Avg. position]) / [Impressions]
 

I also created a measure like this:

 

mWeightedAverage = (SUM(Sheet2[Impressions]) * AVERAGE(Sheet2[Avg. position])) / SUM(Sheet2[Impressions])

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


dyingod Frequent Visitor
Frequent Visitor

Re: AdWords avg. position calculation

Wow! Such a simple solution! Thank you so much for all your help smoupre!! The figures are pretty close to our Excel reports (we're switching from Excel to Power BI). Interesting that instead of a sum, simply switching [Avg. position] to an average does the trick. Neat! Thanks again! Smiley Happy

luchi505 Frequent Visitor
Frequent Visitor

Re: AdWords avg. position calculation

hello 

do you know how this could possibly work with Search Console data 

 

where I have

 

Keyword / total position / clicks / impressions

 

 

 

 


@dyingod wrote:

Wow! Such a simple solution! Thank you so much for all your help smoupre!! The figures are pretty close to our Excel reports (we're switching from Excel to Power BI). Interesting that instead of a sum, simply switching [Avg. position] to an average does the trick. Neat! Thanks again! Smiley Happy