New Member

8 years ago

Solved

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

v-juanli-msft

8 years ago

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

8 years ago

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

v-juanli-msft

Community Support

8 years ago

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

New Member

8 years ago

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

Community Support

8 years ago

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

Forum Discussion

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

3 Replies

Related content

Average - Distinct Count

Counting nulls

Average and Moving Average

Weekly Average

Accumulated average