Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have to sum the time in a particular format.
for Example :
9:26:22
9:22:14
9:12:27
9:05:25
9:15:24
Sum is 46:21:52 - i have to get in this format.
But, when i am trying to do in power i am getting the sum as 22:21:52.
i am trying to change the format in power bi, i am not getting the sum(46:21:52).
In Excel we are changing the format to get sum like(46:21:52) which is [h]:mm:ss:@
These types of formats are not available in Power bi.
is there any alternate solution for this.
Solved! Go to Solution.
Hi @Anonymous
You may add Table1 in your formula. Attached the sample file.
New Time = VAR a = SUMX ( Table1, HOUR ( Table1[Time] ) * 3600 + MINUTE ( Table1[Time] ) * 60 + SECOND ( Table1[Time] ) ) VAR hours = TRUNC ( a / 3600 ) VAR minutes = TRUNC ( MOD ( a, 3600 ) / 60 ) VAR seconds = MOD ( a, 60 ) RETURN hours & ":" & minutes & ":" & seconds
Regards,
Cherie
Hi,
Recently i am facing one issue, I am not able share the dashboards to other people in my team and everyone are having the pro license, but still not able share the dashboard through the OUTLOOK.
Please reply me ASAP.
thanks and Regards,
Damodar Reddy
Hi @Anonymous
You may create below measure.
NewTime = VAR a = SUMX ( 'Time', HOUR ( 'Time'[Time] ) * 3600 + MINUTE ( 'Time'[Time] ) * 60 + SECOND ( 'Time'[Time] ) ) VAR hours = TRUNC ( a / 3600 ) VAR minutes = TRUNC ( MOD ( a, 3600 ) / 60 ) VAR seconds = MOD ( a, 60 ) RETURN hours & ":" & minutes & ":" & seconds
Regards,
Cherie
New Time = var a = SUMX(Hour(Table1[Time])*3600 + MINUTE(Table1[Time])*60 +SECOND(Table1[Time]) var hours = TRUNC(a/3600) var minutes = TRUNC(MOD(a,3600)/60) var seconds = MOD(a,60) return hours & ":" & minutes & ":" & seconds
i am using two columns one is date & time
Date Time
1/7/2017 9:16:25
2/7/2017 9:21:30
3/7/2017 9:22:45
when i an using as columns i am not able to get the name of the column SumX(Hour(i am not getting here that column(Time)).
if i create a measure like Sum(table1(time)). then i can use that measure over here SUMX(Hour(Table1[Time])*3600. Even tough i am using this i am getting error i.e, your are using to many hour arguments.
Please help on this.
Hi @Anonymous
You may add Table1 in your formula. Attached the sample file.
New Time = VAR a = SUMX ( Table1, HOUR ( Table1[Time] ) * 3600 + MINUTE ( Table1[Time] ) * 60 + SECOND ( Table1[Time] ) ) VAR hours = TRUNC ( a / 3600 ) VAR minutes = TRUNC ( MOD ( a, 3600 ) / 60 ) VAR seconds = MOD ( a, 60 ) RETURN hours & ":" & minutes & ":" & seconds
Regards,
Cherie
Hi,
Example : In Excel - 4:46:35 we can change that format to 64:46:35(Custom format - [h]:mm:ss;@)
But in power BI how can we change that this time (4:46:35) to 64:46:35.
Please reply ASAP.
Hi @Anonymous
There's no custom format in power bi.You may create a calculated column to get it.
Regards,
Cherie
I have done a bunch of different stuff regarding time/duration type of stuff. Check out these links:
https://community.powerbi.com/t5/Quick-Measures-Gallery/TIMEADD/m-p/585280
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |