Need help in displaying duration data type as well as on a line chart.

[shadedfern]

Hi All,

Super beginner here, but excited to learn! I am trying to make a line chart showing the average 'duration' of a transaction (multiple transactions take place in a day) over a whole month. I first imported my spreadsheet:

Here the 'Duration' column is the time it took for the transaction. The 'Date_Time' column shows the day the transaction took placed and what time it was submitted at.

Once I import that spreadsheet to PowerBI, I edit the data types (since it doesn't import with the correct data type) in Power Query Editor.

For future reference, from left I shall order column 1, 2, 3, 4

For reference I shall order the columns starting from far left: 1, 2, 3, 4. (1: ID for transaction, 2: Duration of Transaction, 3: Date of Transaction, 4: Time transaction was processed at, not duration). Column 2 is the 'Duration' from the spreadsheet above. I should be seeing seconds like what it shows in the spreadsheet, but instead I see this. Also the format I am looking for in duration is: hh:mm:ss, (I think the max transaction time is around 20 minutes).

The other part of this is when I want to display a line chart. I am trying to show the average transaction time per day for the month of July. The spreadsheet I have imported has multiple transaction times per day for the whole month of July. 

I have also compiled another table in excel with the 'outliers' for transaction time in it, hoping to make a line chart from this. This table has one transaction time per day as opposed to the multiple transactions in the table above. I noticed that when trying to display duration in the line chart it only shows SUM, AVG, etc. The AVG feature is useful for the first chart (average transaction times per day). However is there a way to just display each value per day since the Outliers table only has one transaction time per given day in a month?

Thanks in advance!!!!!

[123abc]

It's great that you're excited to learn Power BI! Creating a line chart to display the average 'duration' of transactions over a month can be achieved by following these steps:

1. Data Import and Transformation:

   • As you mentioned, you should first import your spreadsheet into Power BI.
   • Once the data is imported, you can edit the data types in Power Query Editor to ensure that 'Duration' is recognized as a time data type.

2. Date Hierarchy:

   • In Power BI, you should create a date hierarchy by combining the 'Date' and 'Time' columns to have a single datetime column.

3. Creating a Measure for Average Duration:

   • To calculate the average transaction time per day, you need to create a measure that computes the average duration. To do this, go to the Data view, and under your table, right-click on "New Measure" and use DAX (Data Analysis Expressions) to create the measure. For instance, you can use the AVERAGEX function:

Average Duration = AVERAGEX(YourTable, YourTable[Duration])

1. Creating a Line Chart:

   • Now, go to the Report view.
   • Create a line chart.
   • On the Axis, use your date hierarchy.
   • On the Values, use the 'Average Duration' measure that you just created.

2. Displaying Outliers:

   • To display individual transaction times for the outliers table, you can create another line chart. Use the 'Date' column on the Axis, and the 'Duration' column from the outliers table on the Values. This chart will show individual transaction times for each day.

In your line chart, you can further customize the format to display the 'Duration' as hh:mm:ss by formatting the data field in the line chart settings. Make sure to set the axis format to "Time" in the chart settings to reflect the hh:mm:ss format.

This should help you create the desired line chart with the average transaction times per day and another line chart to display individual transaction times for outliers. If you face any issues or need further assistance, please let me know!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[shadedfern]

I figured out how to fix the duration column to reflect the actual minutes and seconds. However the newest problem I am running into is trying to make it so powerBI does not summarize or COUNT the duration values. I am trying to make a line chart show the actual duration value corresponding to the day.

As you can see in the screenshot above, the table on the right shows all the correct values in the appropriate format. However what is shown on the line chart is the count per day which is 1 instead of individual values shown in the table on the right.

In the Data tab I set the duration column to 'Don't Summarize', this does not do anything in terms of correcting the visual.

Also in the Report tab, when I right click the Duration field under the Values for the line chart I see this:

With no option to 'Don't Summarize'

Is there any fix??

[shadedfern]

Hi,

First of all thank you for responding! I was able to follow all the steps: Import spreadsheet in PowerBI, use Power Query Editor to change the data type of the duration column to 'Duration'. Surprsingly when I change the data type it shows how it is supposed to (in seconds). However, when I click close & apply, thats when the time gets all messed up. I also combined the date and time transaction took place into a date/time column. I also made a new measure for Average Duration using the DAX statement you provided.

One thing however, is that you mention that it is possible to customize the format to display 'Duration' as hh:mm:ss by formatting the data field in the line chart settings.

I do not see where to set the axis format to 'Time'... I am hoping if I can figure out how to fix the units displayed to hh:mm:ss, then my problem will be solved!

*Also side question for the x-axis it shows in numbers in mutliples of 5. Is it possible to make it so it says July 1, July 2, July 3... all the way till the end of the month (in the screenshot below). As you can see the y-axis is messed up since most of the durations per day is in seconds not in the 0.0001, 0.0002 format that is shown below...