Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dyingod
Helper I
Helper I

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

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.