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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

how to create customize measure(minutes) using seconds column

Hi,

 

i have the following table

 

Name    Call_duration_Seconds                                                                                     Expected output

 

   A         3

   A         58                                                                                                                        A=3+58+36+4=1min 41 sec(101)

   A         36                                                                                                                                                =2 mins

   A         4

   B         11

   B         18

   B          4

 

if particular person sum of seconds, greater than or equal to 30 seconds it convert into 1 minute.

if particular person sum of seconds equal to 80 seconds(1m:20s) convert into 1 minute

if particular person sum of seconds 90(1m:30s) seconds convert into 2 minute

if particular person sum of seconds lesser than 30 seconds it convert into 0 minute.

 

how to create customization measure that  includes above scenarioes.

 

My expected output as following:

 

 

Screenshot_5.png

 

        

 

 

 

 

 

 

 

 

 

 

 

 

 

is there is possible using ceiling and floor function.

 

1 ACCEPTED SOLUTION
MarkLaf
Solution Sage
Solution Sage

You can use the ROUND function just like in Excel:

Call duration by mins = 
ROUND(SUM(Table[Call_duration_Seconds])/60,0)

 In English: sum aggregation of seconds, divide seconds by 60 to get the decimal number of minutes (e.g. 90 seconds => 1.5 minutes), then round to the nearest whole number minute (e.g. 1.5 minutes => 2 minutes, 1.49 minutes => 1 minute)

View solution in original post

2 REPLIES 2
MarkLaf
Solution Sage
Solution Sage

You can use the ROUND function just like in Excel:

Call duration by mins = 
ROUND(SUM(Table[Call_duration_Seconds])/60,0)

 In English: sum aggregation of seconds, divide seconds by 60 to get the decimal number of minutes (e.g. 90 seconds => 1.5 minutes), then round to the nearest whole number minute (e.g. 1.5 minutes => 2 minutes, 1.49 minutes => 1 minute)

SteveCampbell
Memorable Member
Memorable Member

Time and dates are stored internally as decimals. One day (24 hours) is a value of 1, half day (12) is 0.5 etc.

 

Therefore, you can use MROUND, which rounds to a multiple. The multiple you would want is 1 / 24 / 60 (one day divided by hours then minutes). Your measure could look something like:

 

Measure:=

MROUND( SUM(Table[Call_duration_Seconds]), 1/24/60)


Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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