cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Counting same values in one Column then averaging the Response time associate with the value

Good Day,

I'm very new to this but any assistance would be greatly appreciated! And yes I'm taking some courses to learn more and hopefully help out in the future!

I have a column F that has device names (DevHostNames), the device names repeat as there might be multiple entries (incidents) against a device. I would like to find out or get some hints on how to Calculate the # of devhostnames then in Column Z (ResolveTime) to calculate the average time to resolve per device.  So let's say the device

 CA-Toronto-GIT

appears 4 times in Column F, so in Column Z there would be 4 resolve times associated with each entry.

 2days, 8hours, 21mins 0days, 22hours, 46mins 7days, 0hours, 54mins 2days, 6hours, 16mins

How would I do this to show CA-Toronto-GIT average response time is?

Column F example

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Counting same values in one Column then averaging the Response time associate with the value

Hi @zork212

In query editor

1.add a custom column to copy “ResolveTime” column

2.select the added “custom” column and click Transform->split column-> by Delimiter

3.select “custom.1” and right-click->Replace value

Rename the column

(same steps for the other two custom column”)

4.Then change data type to number for the three columns

In Data Model View

Create a calculated column

```average response time =
VAR avgday =
CALCULATE (
AVERAGE ( Sheet1[days] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR avghour =
CALCULATE (
AVERAGE ( Sheet1[hours] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR avgmin =
CALCULATE (
AVERAGE ( Sheet1[mins] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
RETURN
CONCATENATE (
CONCATENATE (
CONCATENATE (
CONCATENATE ( CONCATENATE ( avgday, "days" ), "," ),
CONCATENATE ( avghour, "hours" )
),
","
),
CONCATENATE ( avgmin, "mins" )
)```

here is my pbix

Best Regards

Maggie

Community Support Team

## Re: Counting same values in one Column then averaging the Response time associate with the value

Hi @zork212

You need modify the formula as below

```average response time =
VAR avgday =
CALCULATE (
AVERAGE ( Sheet1[days] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR round =
ROUND ( [avgday], 0 )
VAR sub = [avgday] - [round]
VAR subhour = [sub] * 24
VAR avghour =
CALCULATE (
AVERAGE ( Sheet1[hours] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR finalhour = subhour + avghour
VAR avgmin =
CALCULATE (
AVERAGE ( Sheet1[mins] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
RETURN
CONCATENATE (
CONCATENATE (
CONCATENATE (
CONCATENATE ( CONCATENATE ( round, "days" ), "," ),
CONCATENATE ( finalhour, "hours" )
),
","
),
CONCATENATE ( avgmin, "mins" )
)```

an example to see how it works to achieve your requirement

Best regards

Maggie

3 REPLIES 3
Community Support Team

## Re: Counting same values in one Column then averaging the Response time associate with the value

Hi @zork212

In query editor

1.add a custom column to copy “ResolveTime” column

2.select the added “custom” column and click Transform->split column-> by Delimiter

3.select “custom.1” and right-click->Replace value

Rename the column

(same steps for the other two custom column”)

4.Then change data type to number for the three columns

In Data Model View

Create a calculated column

```average response time =
VAR avgday =
CALCULATE (
AVERAGE ( Sheet1[days] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR avghour =
CALCULATE (
AVERAGE ( Sheet1[hours] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR avgmin =
CALCULATE (
AVERAGE ( Sheet1[mins] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
RETURN
CONCATENATE (
CONCATENATE (
CONCATENATE (
CONCATENATE ( CONCATENATE ( avgday, "days" ), "," ),
CONCATENATE ( avghour, "hours" )
),
","
),
CONCATENATE ( avgmin, "mins" )
)```

here is my pbix

Best Regards

Maggie

Frequent Visitor

## Re: Counting same values in one Column then averaging the Response time associate with the value

Hi Maggie,

thank you very much! It's greatly appreciated. The solution works but I might of missed or screwed something up...

for average response time, I get (on some of them) 1.45324234 days 7.334234 hours 20 mins... Just trying to figure out how to round it out and add the hours together so days just shows a full number.

Kindest Regards,

Zork

Community Support Team

## Re: Counting same values in one Column then averaging the Response time associate with the value

Hi @zork212

You need modify the formula as below

```average response time =
VAR avgday =
CALCULATE (
AVERAGE ( Sheet1[days] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR round =
ROUND ( [avgday], 0 )
VAR sub = [avgday] - [round]
VAR subhour = [sub] * 24
VAR avghour =
CALCULATE (
AVERAGE ( Sheet1[hours] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
VAR finalhour = subhour + avghour
VAR avgmin =
CALCULATE (
AVERAGE ( Sheet1[mins] ),
ALLEXCEPT ( Sheet1, Sheet1[DevHostNames] )
)
RETURN
CONCATENATE (
CONCATENATE (
CONCATENATE (
CONCATENATE ( CONCATENATE ( round, "days" ), "," ),
CONCATENATE ( finalhour, "hours" )
),
","
),
CONCATENATE ( avgmin, "mins" )
)```

an example to see how it works to achieve your requirement

Best regards

Maggie

Announcements