cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Averages not showing properly

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

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Frequent Visitor

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

Community Support

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.

Solution Specialist

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)

Your Kudos are much appreciated! Proud to be a Solution Supplier!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors