cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate a weekly average from measures based on multiple columns

Hi everyone,

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.

Any help much appreciated!

5 REPLIES 5
Moderator

## Re: Calculate a weekly average from measures based on multiple columns

Hi @SimonJacobs,

In your screentshot, the date 20/01/2017 week begining date is 19/06/2017? What does this mean? Which week total do you need to calculated? Which week average do you need to calculated?

Regards,

Charlie Liao

## Re: Calculate a weekly average from measures based on multiple columns

First you should unpivot your data so it looks like this

date.  Interaction type.  Qty

then you need a calendar table (sounds like you have one). Join on the date column on both tables.

The measure are then easy.

Total interactions = sum(table[qty)

total days = distinctcount(table[date])

avg interactions per day = divide([total interactions],[total days])

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Regular Visitor

## Re: Calculate a weekly average from measures based on multiple columns

Hi Charlie,

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:

Weekly Interactions =
SUMMARIZE(
'Weekly Interactions',
DateRef[WeekBeginning],
"Total Weekly Interactions",
sum('Social Data'[Total Interactions]),

"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.

Highlighted
Regular Visitor

## Re: Calculate a weekly average from measures based on multiple columns

Hi Matt,

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.

MVP

## Re: Calculate a weekly average from measures based on multiple columns

It is basically a native functionality of the Power Query user interface.

I always try to get my data unpivoted - it makes data analysis MUCH easier.

Announcements