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

[zork212]

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

[v-juanli-msft · Accepted solution]

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

[v-juanli-msft · Accepted solution]

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

[zork212]

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

[v-juanli-msft · Accepted solution]

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