Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Here is what I'm working with Dsp and Ons are in time format, the data set has over 1M records
Unit Dsp Ons
Unit 1 00:51:58 00:58:10
Unit 2 13:28;40 13;29:20
Unit 1 18:22:36 18:29:32
I want to find the difference between DSP and ONS and display the average time per Unit on a bar chart.
I have spent hours searching with no answer.
Thanks in advance
Solved! Go to Solution.
If they are truly date/time data types then you can simply subtract them to find the difference. Date time values are just decimal values where the integer portion is the number of days since December 30th, 1899 and the decimal portion is the time component from minight.
Yeah, unfortunately that last part has been a problem in Power BI for forever. Best thing that you can probably do is to have a difference column set to a decimal type. Use that for your average. Then, create a measure that also averages that column, AVERAGEX('Table',[Diff]) and then format that into a time/duration type using DAX and put it into a tooltip. This looks like a measure aggregation problem.
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
If they are truly date/time data types then you can simply subtract them to find the difference. Date time values are just decimal values where the integer portion is the number of days since December 30th, 1899 and the decimal portion is the time component from minight.
Ok, That worked great. Now how can I find the average of the times for each unit on a bar chart?
Now I have:
Unit response time
unit 1 00:01:25
unit 2 00:04:24
unit 1 00:05:25
unit 3 00:06:35
unit 1 00:07:23
Each Unit has many entries and I need to find the average response time for each Unit
Yeah, unfortunately that last part has been a problem in Power BI for forever. Best thing that you can probably do is to have a difference column set to a decimal type. Use that for your average. Then, create a measure that also averages that column, AVERAGEX('Table',[Diff]) and then format that into a time/duration type using DAX and put it into a tooltip. This looks like a measure aggregation problem.
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |