Short version: How can I make the first graph show the same total as the second graph?
I'm trying to do calculations based on a rolling average number of subscribers. I have set up an example to better explain my problem.
In my example, I have data on the number of subscribers to a streaming service, and how many hours of each genre is watched each month. I want to combine this data to calculate:
1. The average number of hours watched per subscriber
2. The average numbers of hours watched per subscriber, divided by genre
My data only has the subscriber count at the end of each month, so I take the average of the incoming subscriber count and outgoing subscriber count for each month.For this I use rangeavg() and above():
So 1. is solved by this formula:
For 2. however, the forumla does not work anymore, probably because the above() function stops working correctly. This is what I get:
The total does not add up. I would like to divide the hours watched by the total (average) number of subscriptions, to see each genres contribution to the average hours watched.
I have tried different variations of TOTAL, but can't get it to work. I have also read this very good documentation on rolling averages, but have not been able to solve this.
Do I need to calculate the rolling average number of subscribers in my load script instead? If so, how would I do that?
It might help to know the expected output (numeric value) for each Genre in the stacked bar chart for first three to four months. Right now, I understand that the total value needs to match, but I am still trying to understand what the breakup by Genre is going to be