AdWords avg. position calculation

[dyingod]

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

[Greg_Deckler]

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

[dyingod]

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! 

[Greg_Deckler]

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

[dyingod]

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

[Greg_Deckler · Accepted solution]

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])

[dyingod]

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! 

[luchi505]

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! 

---