cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SimonJacobs Regular Visitor
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!

 

 

social data.png

 

5 REPLIES 5
Moderator v-caliao-msft
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?

 

Please providue us more information about you expected result, so that we can make further analysis.

 

Regards,

Charlie Liao

Super User
Super User

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.
SimonJacobs Regular Visitor
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.Data Sample.jpg

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.

SimonJacobs Regular Visitor
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.

Datatouille Established Member
Established Member

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

Hi @SimonJacobs

 

See this article about Pivot & Unpivot: http://radacad.com/pivot-and-unpivot-with-power-bi

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.

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 362 members 3,815 guests
Please welcome our newest community members: