Forum Discussion

DazzaG's avatar
DazzaG
New Member
3 years ago

assistance with DAX code please

hi team, im fairly new to power BI and am helping my colleague with some reporting.  We are using a report created by someone else who has since left the organisation. The  report displays some averages using DAX query but the results appear to be very generous or low. I believe that something in the DAX formular or the way the data is displayed isnt right and would appreciate some assistance trying to work through it.

 

One example is Average Manger approval time which the result is 00:01:49 . Looking at the data [Manager Approval] it should be alot highter. A snippet of the [Manager Approval] data is below. Thats not all the data just a sample

 

 

 

 the DAX query is 

 

Avg Manager Approval Time =

VAR DecimalMinutes = AVERAGE(TimeIntelligence[Manager Approval])

VAR Duration = DecimalMinutes * 60

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

IFERROR(
    IF (DecimalMinutes>0,
    H & ":" & M & ":" & S,
    "0:00"),
    "Error")

 

thanks in advance

Need Help

1 Reply

  • mickey64's avatar
    mickey64
    Super User
    3 years ago

    I think this DAX formular is wrong.

    VAR DecimalMinutes = AVERAGE(TimeIntelligence[Manager Approval])

     

    [Manager Approval] shows hh:mm.

    In this formular, 6 hours is 06:00 (Decimal: 0.25). 

     

    I didn't understand what the person left the organisation wanted to do.

    Insted I show a simple formular.

     

    Avg Manager Approval Time = AVERAGE([Manager Approval])