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

[Anonymous]

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. 

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

[Anonymous]

@Anonymous,

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

AveragePosition = AVERAGE(Sheet1[Position])



Regards,

[Anonymous]

Hello @Anonymous

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.

Any ideas will be much appreciated.

Regards

George

[Anonymous]

@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,

[Anonymous]

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