Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Funnel Data Format

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.0000000303
2019-12-24 20:00:00.0000000305
2019-12-24 21:00:00.0000000305
2019-12-22 21:00:00.0000000307
2019-12-22 20:00:00.0000000311
2019-12-24 19:00:00.0000000312
2019-12-23 05:00:00.0000000314
2019-12-23 08:00:00.0000000314

 

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.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

6 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    5 years ago

    Anonymous ,

    Try like below:

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-alq-msft
        Community Support
        5 years ago

        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.