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

Average time, not earliest or latest

We have an Time column, wich has information about how long user took to interate with our interface, the problem, is that when I put this value on Matrix table, on values, it's not possible to select to display as average, instead earliest or latest.

Any clues about it? 

Captura de Tela 2017-02-27 às 18.01.22.png

2 ACCEPTED SOLUTIONS

@Anonymous,

 

It is because the data type is Time. PBI can not take the average of a time value.

 

Does the data in the column need to be Time (like 12:32pm) or does it just represent the minutes that users interacted? In which case the data type can be a number type and you can take the average of that.

 

Best,

 

Alan

View solution in original post

@TomEvers2,

 

Great observation! I was trying to figure out what it was going off of.

 

@Anonymous you can use the simple DAX formula below to create a new calculated column to convert the current decimal column (0.020262458) back to minutes:

 

='Table'[Column] * 1440

 

 

1440 just represents the number of minutes in a day.

 

Best,

 

Alan

View solution in original post

13 REPLIES 13
alanhodgson
Solution Supplier
Solution Supplier

Hey @Anonymous,

 

What is the data type of the field you want the average of? Also, it is a calculated column or a measure? Finally are you working in PBI Desktop or the online service?

 

Thanks,

 

Alan

Anonymous
Not applicable

Hi @alanhodgson,

 

Its a Time datatype.

It's a clean column, i mean, it came direct from my database (SQL SERVER Time DataType)

PBI Desktop

 

TKS

@Anonymous,

 

It is because the data type is Time. PBI can not take the average of a time value.

 

Does the data in the column need to be Time (like 12:32pm) or does it just represent the minutes that users interacted? In which case the data type can be a number type and you can take the average of that.

 

Best,

 

Alan

Anonymous
Not applicable

No, actually, i'm trying to show as minutes, but suffering to convert time to decimal.

@Anonymous,

 

You can change the data type of that column by selecting it in the panel on the right, then navigating to the Modeling ribbon at the top of PBI Desktop and changing the "Data Type" area to a "Decimal Number". See the screenshot below (ignore the sample data):

 

BI14.PNG

 

Once the data type is set to a number, you will be able to add it to the visual and show the average.

 

Hope this helps,

 

Alan

Anonymous
Not applicable

This is the easiest part, I mean, how to display decimal type as minutes?

@Anonymous,

 

Not sure what you mean.... If you only have one time then you will not be able to get the minutes. If you have a start and end time, then you can do some time math to find the difference and that value will be in minutes (depending on how you subtract the start and end time).

 

For testing purposes, you can right click the column in Query Editor and change the data type to "Duration" and the data format to "Minutes".

 

Alan

Anonymous
Not applicable

I Will try to explain:

 

Ticket 1) 29.11 minutes to answer (converting to decimal, it shows 0,020262458)
Ticket 2) 21.02 minutes to answer (converting to decimal, it shows 0,014609529)
Ticket 3) 35.02 minute to answer (converting to decimal, it shows 0,024323576)

 

Since we converted to decimal, now its possible to make the average of the numbers, but how can I display as minutes? I mean, given the example above, it should display 28 minutes, how to achieve that?

 

Tks

 

@Anonymous,

 

Can you post some sample data of that column so I can see what the data looks like? Definitely has something to do with the data type.

 

Thanks,

 

Alan

The decimal number you are getting is basically a fraction of a day.  You should be able to use a DAX formula to convert it into a time.  This page might be helpful:

https://social.technet.microsoft.com/wiki/contents/articles/679.power-bi-dax-date-and-time-functions...

 

Anonymous
Not applicable

Here is it:

With this data, I built an table that shows agregated data and want to display average time that users took to answer those tickets

 

Captura de Tela 2017-02-27 às 19.25.45.png

 

The decimal number you are getting back is essentially a fraction of a day.  You should be able to convert it back to something you recognize using a dax function.  This page should help:

https://social.technet.microsoft.com/wiki/contents/articles/679.power-bi-dax-date-and-time-functions...

 

@TomEvers2,

 

Great observation! I was trying to figure out what it was going off of.

 

@Anonymous you can use the simple DAX formula below to create a new calculated column to convert the current decimal column (0.020262458) back to minutes:

 

='Table'[Column] * 1440

 

 

1440 just represents the number of minutes in a day.

 

Best,

 

Alan

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.