I'm looking to calculate a weekly average from daily data spanning multiple columns. (Data snapshot below). I have 2 tables one containing social media data and another as a date look up to allow for data aggregation on visuals (e.g. chart data by week beginning, week end, month, quarter etc.).
What I want do is display the weekly average interactions (i.e. work out a Total Interactions across all 6 measures for each week then take an average of the weekly totals.) I have measurs for total Facebook interactions, total Twitter interactions and Total interactions but these are not specific to a time period unless the visual filters or aggregates them. I can't see how to get from these measures to a weekly average.
Ah that's a Typo. I've amended in the photo below.
What I'm looking to do is sum the interactions for each day (e.g. summing all columns on the 23/1/17 gives 828,027 interactions. Then sum the total interactions across the week (so 1.7m for week beginning 23/1/17) then work out the average weekly number across all weeks in a defined period -so in the example above summing the total interactions for the weeks 23/1 and 30/1 then dividing by 2 to give 2,269,401 interactions.
Since posting this I tried creating a summary table using the syntax below:
"Number of days in week", count('Top Gear'[Total TG Interactions]) )
'Social Data' is the name of the table show in the image, 'DateRef' is my date table shown in the image above and 'Total Interactions' is a column I added to sum the interactions across all columns shown above. I also added the 'number of days in week' column in the summary table so I can later filter out any partial weeks due to data updates occuring mid-week.
From the summary table I calculated the weekly average using the measure below:
.Avg Weekly Interactions = AVERAGEX(FILTER('Weekly Interactions','Weekly Interactions'[Number of days in week] = 7),'Weekly Interactions'[Total Weekly Interactions]).
This seems to be working but I'm keen to know if this is a bit long-winded or if there are any better solutions.
Thank you for the feedback. I'm actually looking to calculate the average interactions per week. I think I've found a solution (described above) but would be interested in your thoughts on any improvements/better approaches. Also Could you tell me how to unpivot the data - I've struggled with getting people I work with to provide data unpivoted as it's more user-friendly for them to produce it in pivot format. However I find it much easier to work with unpivoted so am very keen to understand how to do that.