Forum Discussion

cfosund's avatar
cfosund
Icon for Helper I rankHelper I
5 years ago
Solved

Table visual shows wrong average total with dax measure

Hi,  I'm having trouble getting the correct average total value in a table visual when I create the following DAX Measure:  Avg time in minutes = VAR Name1 = CALCULATE(AVERAGE('Timestamp'[Time...
DATEDIFF
DAX
measure
  • AlB's avatar
    AlB
    5 years ago

    cfosund 

    Try this new measure, based on the one you already have:

    Avg time in minutes TOT = 
AVERAGEX(DISTINCT(Timestamp[Event Id]), [Avg time in minutes])

    See your modified file attached

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

cfosund , Try like

 

Avg time in minutes = 
VAR Name1 = CALCULATE(Min('Timestamp'[Timestamp value]),Timestamp[Timestamp name]="Name 1")
VAR Name2 = CALCULATE(MAX('Timestamp'[Timestamp value]),Timestamp[Timestamp name]="Name 2")

RETURN
AVERAGEX(values(Timestamp[Event Id]), IF( NOT( ISBLANK( Name1 ) ) && NOT( ISBLANK( Name2 ) ) , DATEDIFF(Name1,Name2,SECOND)))
cfosund's avatar
cfosund
Icon for Helper I rankHelper I
5 years ago

amitchandak thanks for the quick reply, almost! 😄
It look great, until I added a few more events. then the column total is incorrect again, I expected it to be an total average = 366100 (I have updated the pbix that is shared with the added timestamps):