Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
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 |
Solved! Go to Solution.
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
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 , 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)))
@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):
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
hi @AlB , thanks for the followup, its expected that the Total row in the table visual shows 166260.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |