Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I have a data set for duration in hh:nn:ss format in my data table. But I am not able to do avg for each month. If i do avg i get in whole number format or genral format which i dont want.
What i need,
I need to get Avg duration for month in hh:nn:ss format. Below is the raw data image. Please let me know.
Show the below in Graphical Format or tabular.
June Avg= Average(june duration)
Aug Avg= Average (august Duration )
Solved! Go to Solution.
Hi @Jeev ,
First create 2 columns as below:
Minutes =
var _minute=MINUTE('Table'[JT Avg])
var _sec=SECOND('Table'[JT Avg])
Return
_minute+_sec/60
Then create a measure as below:
Measure 2 =
var _avermin=AVERAGEX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),'Table'[Minutes])
var _aversec=ROUND(_avermin-ROUNDDOWN(_avermin,0),2)*100
Return
"00:"&CONCATENATE(CONCATENATE(ROUNDDOWN(_avermin,0),":"),ROUND(_aversec,0))
And you will see:
For the related .pbix file,pls see attached.
Hi @Jeev ,
First create 2 columns as below:
Minutes =
var _minute=MINUTE('Table'[JT Avg])
var _sec=SECOND('Table'[JT Avg])
Return
_minute+_sec/60
Then create a measure as below:
Measure 2 =
var _avermin=AVERAGEX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),'Table'[Minutes])
var _aversec=ROUND(_avermin-ROUNDDOWN(_avermin,0),2)*100
Return
"00:"&CONCATENATE(CONCATENATE(ROUNDDOWN(_avermin,0),":"),ROUND(_aversec,0))
And you will see:
For the related .pbix file,pls see attached.
@Jeev - See this Quick Measure - https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
Also this article from ages ago: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
@Jeev , For Duration refer
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
For this month Vs last month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
refer : https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |