Forum Discussion
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])
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.
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.
