Forum Discussion

zork212's avatar
zork212
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 dev...
  • v-juanli-msft's avatar
    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's avatar
    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

v-juanli-msft's avatar
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's avatar
    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's avatar
      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