Calculation of Average Search Engine Ranking

[utsavlexmark]

Let me give the example of data I have with me:

Category	Keyword	Company	URL	Rank
Cat 1	KW 1 of Cat 1	Comany A	url	2
Cat 1	KW 2 of Cat 1	Company A	url	4
Cat 2	KW 1 of Cat 2	Company A	url	3
Cat 2	KW 2 of Cat 2	Company A	url	6

There are many categories, each categories has many keywords and companies.

Now the requirment is a table to show as follows:

Category	Company	Avg Position
Cat 1	Company A	Value
Cat 1	Company B	Value
Cat 2	Company A	Value
Cat 2	Company B	Value

Average Position: The average ranking of your website URLs for the query or queries. For example, if your site's URL appeared at position 3 for one query and position 7 for another query, the average position would be 5 ((3+7)/2). This is to explain the calculation.

There will be filters as "Year" and "Month", value needs to be changed based on selection.

I am not sure how reasonable the request is.

It would be great if someone help me out.

Regards

Utsav

[Ale]

Why wouldn't an AVERAGE(Rank) work here? If you have a table visual with Category, Company and AVERAGE(Rank), it should give you the result you expect.

If I got that wrong, please elaborate a bit more so I can understand the issue.

[utsavlexmark]

Suppose category name is "Solution" it has 25 keywords in it. Company A is ranking for 25 keywords and Company B is ranking for 20 keywords. Sum of all rankings for Company A is 100 and Company B is 140 (as mentioned in original post), for "Solution" category Average Position of Company A is (100/25) 4 and Company B is (140/20) 7.

Dose it make sense?