Funnel Data Format

[Anonymous]

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 :

HourHH = Format('File1'[Date],"HH")

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.

[v-alq-msft]

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.

[Anonymous]

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.

[v-alq-msft · Accepted 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.

[Anonymous]

Hi,

Thank you for your answers, I'll try it and see if this is worth it.

Best regards,

Martin.

[Tahreem24]

@Anonymous ,

Try like below:

Duration = FORMAT(Table[Date],"HH:MM")

[Anonymous]

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.