Forum Discussion

sd0021781's avatar
sd0021781
Regular Visitor
1 year ago
Solved

Net Login calculation

Hi,

 

Need help

 

I have net login without aux in my excel file >> when i am using it after uploading in Power BI, It is not allowing me to calculate average, both the screenshot are attached for your reference.

 

I guess i need to do something in Transform Data. Currently its in time format

 

 

 

 

Need Help
  • sd0021781's avatar
    sd0021781
    1 year ago

    Thanks,

     

    I got my answer with below mentioned syntax

     

    Average Time W/O Aux = VAR FilteredTable =     FILTER(         'One View Noida',         'One View Noida'[Net Login without Aux] <> TIME(0, 0, 0)     ) VAR AvgTime =     AVERAGEX(         FilteredTable,         'One View Noida'[Net Login without Aux]    ) RETURN     FORMAT(AvgTime, "HH:MM:SS")
     
    Thanks

8 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi sd0021781  Set the data type to Time. See image below:

     

    Convert time to decimal either using power query or dax. I have used dax calculated column. See image below:

     

    Write measure to find out average of the given timestamps. Try this:

    AverageTimeDecimal = 

VAR ATD = AVERAGEX(DateTable, DateTable[TimeInDecimal])
VAR AvgHour = INT(ATD)
VAR AvgMinute = INT((ATD - AvgHour) * 60)
VAR AvgSecond = ROUND((ATD - AvgHour - AvgMinute/60) * 3600, 0)
VAR Time = TIME(AvgHour, AvgMinute, AvgSecond)
VAR Result = FORMAT(Time, "hh:mm:ss AM/PM")

RETURN
Result

     You will get your desired output. See image below:

     

    Hope this helps!!
    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz

    • sd0021781's avatar
      sd0021781
      Regular Visitor
      1 year ago

      Thanks for your revert.

      Please find below the error i can see.

       

       

  • sd0021781's avatar
    sd0021781
    Regular Visitor
    1 year ago

    Thanks,

     

    I got my answer with below mentioned syntax

     

    Average Time W/O Aux = VAR FilteredTable =     FILTER(         'One View Noida',         'One View Noida'[Net Login without Aux] <> TIME(0, 0, 0)     ) VAR AvgTime =     AVERAGEX(         FilteredTable,         'One View Noida'[Net Login without Aux]    ) RETURN     FORMAT(AvgTime, "HH:MM:SS")
     
    Thanks