Forum Discussion

astarkey's avatar
astarkey
Frequent Visitor
8 years ago

TIME/Duration conversion & averages

Ok i always seem to struggle with time in BI and I've a project that its quite critical I can display values as hours and minutes, not deciaml time and not just whole minutes...

 

I have a tabel that contains the actual duration as a number and I have converted that into whole minutes, but when I try and create visuals etc... I can return average values that are in hours and minutes, Unless I use 

 

Measure 2 = (FORMAT([hours and mins], "hh:mm"))

 

But that is a text field, Great for Card visuals but do not work in charts.

 

Yet when I run an average across my actaul time coumn I get a decimal figure in my chart.

 

I have tried to format the column with the actual hours and minutes as duration etc... but it goes all over the place.

 

 

Seems like such a simple thing I want to do but I cannot get it to work and I know that displaying the values as decimal won't be considered acceptable

2 Replies

  • Hi astarkey,

     

    How about creating a tooltip based on a measure? Example:  

    tooltip =
    FORMAT ( DIVIDE ( AVERAGE ( 'Table'[Time in Minutes] ), 60 ), "HH:MM" )
    • astarkey's avatar
      astarkey
      Frequent Visitor

      Thanks for the reply

       

      I can get the tooltip to show the correct time already but I don't think thats going to be my solution.... I really need the data