Forum Discussion

Pbiuserr's avatar
Pbiuserr
Post Prodigy
4 years ago

Calculating Resolution time for given categories

Hello,

Trying to calculate resolution time from seconds to hr:sec for only two categories (the rest should give N/A). 

The problem is that its good for all categories so it shows ResTime for Cat1 and Cat2 and N/A for the rest but one - there it gives 0:00

 

Any ideas why it is this way and if my formula works (would calculate ResTime only for records with Cat1 and Cat2)

 

ResTime =

VAR ConvertTime = CONCATENATE(
int(
AVERAGE(data'[RES_TIME])/3600),
CONCATENATE(":", if(INT(MOD(AVERAGE(data'[RES_TIME]),3600)/60)<10,
CONCATENATE("0", INT(MOD(AVERAGE(data'[RES_TIME]),3600)/60)),
INT(MOD(AVERAGE(data'[RES_TIME]),3600)/60))))

RETURN
IF(ResTime = ":0", "N/A",CALCULATE(MTTR, data'[CATEGORY] IN {"Cat1", "Cat2"}))

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Pbiuserr Difficult to say with the information provided. You might have a look here: (1) Chelsie Eiden's Duration - Microsoft Power BI Community

     

    Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      Hey

      I know, unfortunately I can't share the data, let me explain

       

      I got a column with ResTime (mean time to response) provided in seconds. I convert them by my measure to HR:SECONDS format using concatenate, giving it ":" and leading 0 if for example minutes are less than 10 so it'll be 5:05 not 5:5

       

      However, this should be calculated/evaluated and highlighted ONLY on 2 categories/statuses (which have task done, otherwise there supposed to not be value in the column, but oh well, there are for some records and for now its unavailable to fix it) - for the rest of categories/statuses it should give N/A value because in reality you can't measure mean time to solve on something which hasn't been solved

       

      so if i choose other status/category it shows N/A but cat 1 or cat2 it shows this restime

      EDIT: okay, i'll talk with Data engineers to fix the data issue instead of overcome it

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, Pbiuserr 

        I'm quite confused by the code you provided.

        It seems that you didn't call the variable 'ConvertTime', so what's the point of creating it. Is 'MTTR' in the formula a new field? Without a sample data, it is difficult for us to carry out further research.

         

        Best Regards,
        Community Support Team _ Eason