Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
sd0021781
Regular Visitor

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

 

sd0021781_0-1726741503619.png

 

sd0021781_1-1726741623206.png

 

 

1 ACCEPTED SOLUTION
sd0021781
Regular Visitor

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

View solution in original post

8 REPLIES 8
sd0021781
Regular Visitor

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
shafiz_p
Super User
Super User

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

shafiz_p_0-1726743667034.png

 

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

shafiz_p_1-1726743751588.png

 

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:

shafiz_p_2-1726743891460.png

 

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

 

 

Best Regards,
Shahariar Hafiz

Thanks for your revert.

Please find below the error i can see.

 

sd0021781_0-1726744720621.png

 

Remove extra brackets after AM/PM

shafiz_p_0-1726744935780.png

 

removed the extra bracket. still their is no change

again remove extra bracket ] in first line after 'One View Noida'[Net Login Without Aux]

You formula for measure may not work. As I can see that you have used original time column inside averagex. You first need to create a column to convert "Net Login without Aux] to TimeinDecimal. 

Follow steps I have provided. To create column, see the image below:

shafiz_p_0-1726745593286.png

 

first click number 1 and then write formula in number 2. exactly same just need to change column name.

 

Hope this helps!!

Not it is showing Sytax Error

 

sd0021781_1-1726745666205.png

Please first create a calculated column in your orginal data set to convert time into decimal format. Then create measure with the code I have provided.

 

I don't see you are returning the result.

add last line
return
result

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.