How to use DAX expressions tc calculte daily averages (by week and month)

[arzanp]

Hello Team
I have recently started using Powerpivot as I've been allocated an analysis task that looks at call data, which is over 200,000 rows. Using lookup tables to categorise calls crashes excel, so I have used PowerQuery to query the data and place it into a Powerpivot data model.

Assumptions:
Each row is a call
Each call is classified into a call type (eg. Credit Cards - How to pay)
Each call also lists the average handle time of that call, in seconds (eg. 300)

I'm familiar with these functions in regular excel pivot tables, however I'm not too good with DAX expressions.
I have attached some dummy raw data, and also a view of how I'd like the tables to look.

I'd like assistance in determining the correct formula for date grouping analysis highlighted in yellow.

Edit: I can't attach anything, so I'll link to google docs

Sample Data:
https://docs.google.com/spreadsheets/d/1X4lJK1M7zJpVNE8_aRBP5S5xq4nqy8VCs_E1Fbv3QDo/edit?usp=sharing

 

[scottsen]

I am guessing you are suck with 32 bit excel? How much data you dealing with, as crashing Excel with data usually takes... quite a bit.

 

[arzanp]

32bit excel on a 64bit operating system. The raw data in my link, I get two additional numbers with that in two columns. Based on those numbers, I lookup a table to tell me what the call was about. That lookup formula is slow even at 56k rows. I get 7 K rows a day(that's how many calls this place takes) and they want the data to trend for a year. So power pivot really is the only way to go, unless they're willing to invest in SAS (which they're not)

 

[scottsen]

I totally mis-read your post -- I thought Power Pivot was blowing up for you. At any rate, you will certainly have a much easier time with 64 bit if you can arrange that.

I would just start writing your measures and build some simple reports to get a feel for Power Pivot.

Total Call Length := SUM(Facts[CallSeconds])

Avg Call Length := AVERAGE(Facts[CallSeconds])
or
Avg Call Length := [Total Call Length] / COUNTROWS(Facts)

Avg Call Length Per Month := AVERAGEX(VALUES(Calendar[Months]), [Avg Call Length])

 

[arzanp]

Thanks so much Scott. I'll work on this tomorrow and let you know how I go. Now that I know the dax expression to use I'll read up on it