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'[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 IdTimestamp nameTimestamp value
1Name 129.11.2020 01:48:00
1Name 230.11.2020 23:59:00
2Name 1 
2Name 229.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!


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 

6 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    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 

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hicfosund 

    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's avatar
      cfosund
      Icon for Helper I rankHelper I
      5 years ago

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

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