Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculating Average from a measure

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    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.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , 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]))
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    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.