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.
Hi,
I need some help on a graph I'm trying to build.
Here is a sample from my data : the second column is a duration in seconds.
2019-12-24 08:00:00.0000000 | 303 |
2019-12-24 20:00:00.0000000 | 305 |
2019-12-24 21:00:00.0000000 | 305 |
2019-12-22 21:00:00.0000000 | 307 |
2019-12-22 20:00:00.0000000 | 311 |
2019-12-24 19:00:00.0000000 | 312 |
2019-12-23 05:00:00.0000000 | 314 |
2019-12-23 08:00:00.0000000 | 314 |
What I would like to build is a funnel that would give the average time by hour, but in a HH:mm format.
So first I built a calculated column to only get the hour from the first column :
The problem comes from the second column :
-I cannot use a calculated measure to build the graph.
-If I simply use the second column (with average), I get the right result, but not the HH:mm format.
-Using a calculated column like this in the funnel : AvDur = TIME(0,0,AVERAGE('File1'[Duration])) gives a completely different result.
Is there something I'm missing ?
Best regards,
Martin.
Solved! Go to Solution.
Hi, @Anonymous
It is unsupport to put a formatted text in 'Values' of a visual. Only number data type can be put in the 'Values'. I'd like to suggest you calculate the total hours or total minutes as a workaround.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a measure as below.
Avg =
var x =
AVERAGEX(
'Table',
HOUR([Date])*3600+[TotalSecond]
)
var h = INT(x/3600)
var m = INT(MOD(x,3600)/60)
return
h&":"&m
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Allan,
Thank you for your help.
Unfortunately, it doesn't work either.
I built the funnel on your pbix file, but I cannot use the measure "Avg" in the "Values" part.
So I built a calculated column to put instead, but it just displayed the number "1" for each date.
What I'd like to obtain is the funnel but with the HH:mm value on each line.
Do you have something else to try ?
Best regards,
Martin.
Hi, @Anonymous
It is unsupport to put a formatted text in 'Values' of a visual. Only number data type can be put in the 'Values'. I'd like to suggest you calculate the total hours or total minutes as a workaround.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for your answers, I'll try it and see if this is worth it.
Best regards,
Martin.
@Anonymous ,
Try like below:
Duration = FORMAT(Table[Date],"HH:MM")
Hi,
Thank you for your help.
Unfortunately, it doen't work either : the result is the same than with AvDur = TIME(0,0,AVERAGE('File1'[Duration]))
In values, adding the column gets us : "Number of Duration" and we get values such as 344 instead of something like 01:20.
Do you have something else to try ?
Best regards,
Martin.
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |