Table visual shows wrong average total with dax measure

[cfosund]

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'[Timestamp value]),Timestamp[Timestamp name]="Name 1")
VAR Name2 = CALCULATE(AVERAGE('Timestamp'[Timestamp value]),Timestamp[Timestamp name]="Name 2")

RETURN
IF( NOT( ISBLANK( Name1 ) ) || NOT( ISBLANK( Name2 ) ) , DATEDIFF(Name1,Name2,SECOND))

When there are timestamps that does not include a value / Blank, the total gets wrong. 

The "Timestamp" table looks like this:

Event Id	Timestamp name	Timestamp value
1	Name 1	29.11.2020 01:48:00
1	Name 2	30.11.2020 23:59:00
2	Name 1
2	Name 2	29.11.2020 23:59:00

In this example the tables contains two timestamp types, but in the orginal table its over 100.
So I want to solve this with measures without pivoting the timestamp name column. 

I guess there is something I'm missing on the filter context, I have also uploaded a test pbix to easier understand the issue. 
wrong_avg_total.pbix 
Any tips would be awsome, thanks!

[AlB · Accepted solution]

@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 

[cfosund]

Excelente @AlB , that worked! Thank you so much.

[amitchandak]

@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]

@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):

[AlB]

Hi@cfosund 

What exactly is the expected result in the example you show?

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 

[cfosund]

hi @AlB , thanks for the followup, its expected that the Total row in the table visual shows 166260.