cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimJim Regular Visitor
Regular Visitor

Average

Hello,

 

I am trying (and failing) to work out the average execution time of a report. I have a fact table called ReportExecution in the following (simplified) format:

 

Date          ReportName          UserName          ReportCount          AvgerageTime

20/12/17   StockReport           Joe Lewis            6                            31

21/12/17   StockReport           Joe Lewis            5                            36

21/12/17   StockReport           Asim Shah          8                            49

22/12/17   StockReport           Asim Shah          2                            50

 

The table has one row for every user / day / report, ReportCount is the total times a user has run the report for that day.

 

I need to get the actual average and not just an average of an average.

 

If I was doing this in excel I would do something like...

 

1. Get total time for each row by multiplying average time by report count
2. Get the total time for the set by summing the row totals
3. Get the total report count by summing the report count for each row
4. Divide the total time by the total report count

 

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Average

So, maybe create a new column in your fact table:

 

TotalTime = [ReportCount] * [AverageTime]

Then create a measure like:

 

MyAverage = DIVIDE(SUM([TotalTime]),SUM([ReportCount]))

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Average

So, maybe create a new column in your fact table:

 

TotalTime = [ReportCount] * [AverageTime]

Then create a measure like:

 

MyAverage = DIVIDE(SUM([TotalTime]),SUM([ReportCount]))

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

JimJim Regular Visitor
Regular Visitor

Re: Average

Excellent, thank you.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 515 members 4,144 guests
Please welcome our newest community members: