cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
MarkLaf Member
Member

Re: how to create customize measure(minutes) using seconds column

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
SteveCampbell Established Member
Established Member

Re: how to create customize measure(minutes) using seconds column

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)
MarkLaf Member
Member

Re: how to create customize measure(minutes) using seconds column

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 459 members 4,149 guests
Please welcome our newest community members: