Calculate average of the same group and display it using charts

[Anonymous]

Hello everyone,

I have a table with 3 columns, date, keyword and position. The keyword can appear in different dates and every time may have a different position.

What I am trying to do, is to calculate the average for each keyword in a given period, day, week or month. Then i want to display using a line chart or bar chart, how many keywords share the same average position.

So my graph would look like this. Showing that at position 1 (X axis) there are 3 keywords (Y axis) and so on so forth.

Any ideas?

Thank you
George

[MalS]

Depending on how your data is structured, this may help...

Assuming you have data like this:

Date	Keyword	Position
Sunday, January 1, 2017	Data	1
Sunday, January 1, 2017	Chart	1
Monday, January 2, 2017	Data	1
Monday, January 2, 2017	Chart	1
Monday, January 2, 2017	Graph	1
Tuesday, January 10, 2017	Data	1
Tuesday, January 10, 2017	Chart	1
Tuesday, January 10, 2017	Graph	2

You can create a couple of measures to:

1. Count the keywords: Keyword Count = COUNT(Table1[Keyword])

2. Count the total number of days worth of data: Number of days = CALCULATE(DISTINCTCOUNT(Table1[Date]), ALL(Table1[Position]))

3. Get the average position: Average Keyword Count = DIVIDE([Keyword count],[Number of days],0)

Then add Average Keyword Count and Position to a chart (Position on the Axis and Average Keyword Count as values).

You can add a filter to the page to filter by different date periods. 

(Note that the calculation isn't quite right if you don't have data for each day, but maybe it's close enough for you? If not there are other ways to do this)

Regards,

Mal

[Anonymous]

Hello MaIS,

Thank you for sharing with me your solution. 

Is it possible to include in the formula do not count the days where the position is equal to 0? I am not getting the same results as our system is returning and I believe is because it doesn't take in consideration dates with position equal to 0. 

Regards

George

[MalS]

Do you have a sample of data (anonymized if necessary), as well as the results you expect? That will make it easier to figure out.

[Anonymous]

Hello MaIS

Here is the sample data. It is for April.

The result should be like the image below 

Position Keywords

1            1

2            9

3            12

4            14

5            10

etc...

Let me know if you need anything additional.

Regards
George

[MalS]

Hi George,

So that data helps. I can see you don't have data for each keyword on each day, so those equations won't work very well. 

Perhaps a different approach is better.

1. Connect to your Keyword data

2. Edit Queries, right click on Keywords table and click Reference. Rename to something like Grouped Keywords

3. Click Group By, then select Keywords as the column to group by, and create a new Average Position column (name = Average Position, operation = Average, column = Position)

4. Change the data type of Average Position to Whole Number

5. Close and Apply

6. Create a new Measure using this formula: Keyword Count = COUNTROWS('Grouped Keywords') 

7. Create an area chart with Average Position on the axis and Keyword Count on the values

The graph doesn't look as good as the one in your screenshot (e.g. it doesn't handle positions with no data very well), but you can play around with the formatting to see if you can improve it. (Unfortunately PowerBI has some limitations here for now.)

If you have more than one month's data, you could Group By, say, Keywords and Month at step #3 above.

Hope that helps.

Mal

[Anonymous]

Hello MaIS,

Thank you for taking the time to work on the solution. Unfortunately, the result is not what I am looking for.

I will play a bit with the various things you suggested and hopefully, I will be able to make it work.

Thank you once again! Much appreciated.

Regards
George

[MalS]

No problem George. 

If it helps, I thought of a way to get the graph looking a bit better:

1. Create a table that lists all the possible unique positions (from 1 to 450 in the sample data). 

2. Add that to the Power BI file

3. Create a relationship between that and the Average Position field

4. Change the Keyword Count measure formula to: Keyword Count = IF(ISBLANK(COUNTROWS('Grouped Keywords')),0,COUNTROWS('Grouped Keywords')) . This will make the positions where there is no data show as a zero on the graph. 

5. Create an Area graph of Grouped Keywords[Keyword Count] and Position[Position]. It should look like this:

Hope you are able to get it working the way you want.

Mal

[Anonymous]

Hello @MalS

If I may ask, the new table Grouped Keywords doesn't contain a date column now and I cannot create the relationship with my Dates table. This doesn't allow me to filter the data by month. How can I add the date for to the Grouped Keywords table? Do you have a way in mind?

Regards
George

[MalS]

If you just need to get the average each month, then you could just group by month.

First, you need a 'month' field in the Keywords table. One way is to go the Query Editor and select the Date column. Then click Add Column > Date > Month > Start of Month. 

Next, go to the Grouped Keywords table and edit the Grouped Rows step so that it groups by Keywords and Start of Month. 

Then you can add a Start of Month filter to filter by month.