topic Re: Averages not showing properly in DAX Commands and Tips

Averages not showing properly

reemadsouza — Tue, 03 Sep 2024 17:40:03 GMT

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

Re: Averages not showing properly

ahadkarimi — Tue, 03 Sep 2024 19:23:08 GMT

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!

Re: Averages not showing properly

Anonymous — Wed, 04 Sep 2024 03:09:06 GMT

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.

Re: Averages not showing properly

vivek31 — Thu, 05 Sep 2024 06:47:19 GMT

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