cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zork212 Frequent Visitor
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 exampledevicehostname.GIF

 

resolve.GIF

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
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

 

7.png

 

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

   Rename the column

(same steps for the other two custom column”)

 

8.png

 

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

 

9.png

 

In Data Model View

Create a calculated column

 

10.png

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

View solution in original post

Community Support Team
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" )
    )

20.png

an example to see how it works to achieve your requirement

 

 

Best regards

Maggie

View solution in original post

3 REPLIES 3
Community Support Team
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

 

7.png

 

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

   Rename the column

(same steps for the other two custom column”)

 

8.png

 

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

 

9.png

 

In Data Model View

Create a calculated column

 

10.png

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

View solution in original post

zork212 Frequent Visitor
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
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" )
    )

20.png

an example to see how it works to achieve your requirement

 

 

Best regards

Maggie

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)