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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Calculating average value of a column per month using distinct values of an other column

Hello Everyone,

 

This is my 3rd attempt to find an answer to my question. I will try to explain the problem in the best way possible.

 

I have a big table that collects data for keywords per day, such as impressions and Google ranking position day.

What I am trying to do is to calculate the average position for each keyword per month. The same keyword may appear in the table for the same month many times since we are collecting daily data. 

What I want to display then, is an area chart that will have in the X Axis the positions from 1 - 100 and the Y axis will be the number of keywords ranking on each position.

 

The attached image shows the final results. These charts use monthly data which makes it a lot easier. 

footprint.JPG

 

My problem so far is that I cannot find a way to calculate the average position per month for each keyword. I thought that by calculating the SUM of the position column and dividing it by the distinct count of each keyword, was going to work. 

 

Sample data can be found here.

 
Thank you in advance. If you would like me to provide more information please let me know.


Regard
George

4 REPLIES 4
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@Anonymous,

Based on your sample data, you just need to create a measure using the following DAX.

AveragePosition = AVERAGE(Sheet1[Position])
1.PNG


Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello @v-yuezhe-msft

 

Thank you for your reply. I tried to use AVERAGE() in the past. My problem is that i cannot create the chart that shows the number of keywords that rank by position as the one that is posted in the original post.

 

By calculating the average position using the measure you suggested for the Y axis this is the graph that I get.

 

Position-graph.JPG

 

Any ideas will be much appreciated.

 

Regards

George

@Anonymous,

I am not quite clear about your requirement, could you please post the expected result in table based on the sample data(April) you shared?

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello,

 

I have tried many different approaches and suggestions but still, the output is not correct.

Any ideas will be much appreciated.

Thank you in advance

George

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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