Average Column for a converted text column

[Anonymous]

I have a column from a Call Detail Report that imports as a whole number representing the seconds a call takes. 

 

I used http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 to convert that to a new column called a_DurationInMinDAX.  This resuled in what looked like the perfect output.  This outputted as a text column.  Trying to convert it to a time didn't work. 

Then I tried get a Card Visualization to display what the average of that column was. Couldn't get what I was looking for. 

So, then I used the following measurement - AvgCallDuration = AVERAGEX('asteriskcdrdb cdr'[a_DurationInMinDAX], COUNTROWS('asteriskcdrdb cdr'[a_DurationInMinDAX]))  to try and get the average.

This resulted in the following error:

A single value for column 'a_DurationInMinDAX' in table 'asteriskcdrdb cdr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Basically I'm just trying to get an average for my total calls - represented in Minutes - from either the [duration] or [a_DurationInMinDAX] column.

[v-piga-msft]

Hi @Anonymous，

If I understand your issue correctly that you convert the whole number to the text type and you want to convert the text type to time type and get the average value of the seconds, but it doesn't work?

If it is, I have made a test with your logic, it seems that I could get the average value by average function.

---

 So, then I used the following measurement - AvgCallDuration = AVERAGEX('asteriskcdrdb cdr'[a_DurationInMinDAX], COUNTROWS('asteriskcdrdb cdr'[a_DurationInMinDAX]))  to try and get the average.

This resulted in the following error:

A single value for column 'a_DurationInMinDAX' in table 'asteriskcdrdb cdr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

---

It seems that the synax of your measure is incorrect. You could have a view of AVERAGEX function and COUNTROWS function. 

If you still need help, please share your data sample and your desired output.

Best Regards，

Cherry

[Anonymous]

is there a way for me to upload the pbix file?

[v-piga-msft]

Hi @Anonymous,

Sorry for the delay. You could upload your dummy pbix file to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[Anonymous]

Sorry this took so long.  I've uploaded the dummy data to https://www.dropbox.com/sh/lq4oha44im7h9sw/AADDk8JdCt-0LyOAzw9gpRyea?dl=0

Any help is very appreciated.  As a reminder, of what I'm looking to do...

You have the fundamentals for sure.  The issue is the averaging in the table.  Your AvgCallDuration takes all the items in the "duration" column into account.  There are however many call groups and years of call data.  If you average the entire column, it does just that.   I need it to remain dynamic so when I bring it into the report I can filter both dates and call groups.  What I'm looking for is a way to average the Duration_HHMMSS column, but filter that average based off of date range [calldate] and call group [dst]. 

My ultimate goal is to use the Card Visualization to show the average length of calls for a specific call group [dst]=310 and a date range, this year.  My attempt to add Visual and/or Page filters have not proven successful.

[Anonymous]

Hi Cherry,

You have the fundamentals for sure.  The issue is the averaging in the table.  Your AvgCallDuration takes all the items in the "duration" column into account.  There are however many call groups and years of call data.  If you average the entire column, it does just that.   I need it to remain dynamic so when I bring it into the report I can filter both dates and call groups.  What I'm looking for is a way to average the Duration_HHMMSS column, but filter that average based off of date range [calldate] and call group [dst]. 

My ultimate goal is to use the Card Visualization to show the average length of calls for a specific call group [dst]=310 and a date range, this year.  My attempt to add Visual and/or Page filters have not proven successful.

I cannot seem to attach an example to my post for review?