Calculating Average from a measure

[analyst123]

Hello,

I have a dataset where I am trying to find the average of a measure to show it as a card on my dashboard. 

I have used the following measure code to get the time duration between two dates: 

DOWNTIME (MINUTES) = SUMX('Source', CALCULATE(

DATEDIFF(MIN(Source[APPEARED]), MIN(Source[DISAPPEARED]), MINUTE) +

(SECOND(MIN(Source[DISAPPEARED])) - SECOND(MIN(Source[APPEARED]))) / 60

))

I need to calculate the average of this duration based on the count of 'message text'. 

I tried using the following formula:

Measure = AVERAGEX(VALUES(Source[MESSAGE NUMBER]), CALCULATE(

DATEDIFF(MIN(Source[APPEARED]), MIN(Source[DISAPPEARED]), MINUTE) +

(SECOND(MIN(Source[DISAPPEARED])) - SECOND(MIN(Source[APPEARED]))) / 60))

But it does not give me the accurate value. 

 This is an example of the layout:

Here, the average should be 38/23 = 1.65, but instead the average time is 1.25 minutes.

[Anonymous · Accepted solution]

Hi, @analyst123 

I create a sample table:

Then create a new measure and try the following DAX expression:

AverageDowntime = 
DIVIDE(
    SUMX(VALUES('Table'[Message number]), CALCULATE(
        DATEDIFF(MIN('Table'[APPEARED]), MIN('Table'[DISAPPEARED]), MINUTE) +
        (SECOND(MIN('Table'[DISAPPEARED])) - SECOND(MIN('Table'[APPEARED]))) / 60
    )),
    DISTINCTCOUNT('Table'[MESSAGE NUMBER])
)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@analyst123 , Try below DAX

Average Downtime (Minutes) =

DIVIDE(

    SUMX(

        VALUES(Source[MESSAGE NUMBER]),

        CALCULATE(

            DATEDIFF(MIN(Source[APPEARED]), MIN(Source[DISAPPEARED]), MINUTE) +

            (SECOND(MIN(Source[DISAPPEARED])) - SECOND(MIN(Source[APPEARED]))) / 60

        )

    ),

    COUNTROWS(VALUES(Source[MESSAGE NUMBER]))

)