Comparing dynamic weekly data with static benchmark

[SebastianLook]

Hi Everyone!

I’m looking for support on a quite simple calculation (I believe). Many thanks in advance for your help!

I’m looking for a calculation of % to goal on a weekly basis.

Let’s say actual revenue per store = dynamic value in week 1, 2, 3, etc. 

This dynamic value should be compared with an average goal per week = static value, which is the same for every week throughout the year, but different from store to store. Each week should be compared to this. (I don’t know where and how this would be defined?)

Result of the calculation = % to goal = dynamic value / static value

In case the table is sorted not by store name column, static value associated to store should be moving with it of course.

weeks:         week 1                      % to goal              week 2.    % to goal   week 3   % to goal  ...

store 1.       dynamic value       static value

store 2

store 4

store 3

store 6

…

Thanks again for your quick help!

Sebastian