CODEWITHSUNDEEP
powerbidax
mcfred
mcfred

Reputation: 1401

Changing Average of Seconds in minutes and seconds

I have a table below:

enter image description here

I am trying to get the average of "someNumber" (which is number of seconds) per date and then convert that number into miutes and seconds.

So, I am drawing the graph below:

enter image description here

As you can see, I used the average filter and it calculated the average of "someNumber" per date. For instance, the average on 1st is 13. Now, how do I convert 13 into minutes and seconds so that I can display it as a tooltip?

Upvotes: 0

Views: 655

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Create a measure to get the average in seconds.

avg = AVERAGE(Table[SomeNumber])

Then create a measure for the tooltip lets call it Time, this helpful measure was created by @konstantinos and @smoupre in the Power BI community blog.

Time = 
VAR Duration = [avg] // Here use the average measure created before
VAR Hours =
    INT ( Duration / 3600)
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) 
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
      CONCATENATE ( M, CONCATENATE ( ":", S ) )

Now use the [Time] measure as tooltip in your chart

enter image description here

Hope it helps.

Upvotes: 1

Related Questions