Averages not showing properly

[reemadsouza]

I have this measure :-

row context avg =

AVERAGEX(

    FILTER(

        'Ticket Times',

        NOT(ISBLANK('Ticket Times'[Trip Time (s)]))

    ),

    'Ticket Times'[Trip Time (s)] / 60

)

 and another way to write the same 

Column context avg = CALCULATE(DIVIDE([Trip Time (min)],[Ticket Count without Blanks]))

where 

Ticket Count without Blanks =

CALCULATE(

    DISTINCTCOUNT('Ticket Lines'[Ticket Number]),

    FILTER('Ticket Times', NOT(ISBLANK('Ticket Times'[Trip Time (s)])))

)

for 3 days it gives me these values 

 

 when I average in excel I get 

  how do i get my pbi measuire to match 145.75388 avg instead of 145.788

[vivek31]

Hii, @reemadsouza

you can try this,

first sum of count

total count = SUM('Table (3)'[count])

second avg the total count,

avg = AVERAGEX(VALUES('Table (3)'[id]),[total count])

[Anonymous · Accepted solution]

Hi @reemadsouza ,

I create a table as you mentioned.

Then I create a new table and here is the DAX code.

Table 2 = 
SUMMARIZE(
    'Table', 
    'Table'[ID], 
    "TotalCount", SUM('Table'[Count])
)

So you can calculate what you want.

Average = AVERAGE('Table 2'[TotalCount])

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ahadkarimi]

Hi @reemadsouza, give this measure a try, and if you encounter any issues, let me know.

Corrected Avg = 
VAR TotalTimeInMinutes = SUMX(
    FILTER(
        'Ticket Times',
        NOT(ISBLANK('Ticket Times'[Trip Time (s)]))
    ),
    'Ticket Times'[Trip Time (s)] / 60
)

VAR NonBlankTicketCount = 
    CALCULATE(
        DISTINCTCOUNT('Ticket Lines'[Ticket Number]),
        FILTER('Ticket Times', NOT(ISBLANK('Ticket Times'[Trip Time (s)])))
    )

RETURN
DIVIDE(TotalTimeInMinutes, NonBlankTicketCount)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!