- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Average
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-26-2018 05:03 AM
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.
Solved! Go to Solution.
Accepted Solutions
Re: Average
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-26-2018 05:28 AM
So, maybe create a new column in your fact table:
TotalTime = [ReportCount] * [AverageTime]
Then create a measure like:
MyAverage = DIVIDE(SUM([TotalTime]),SUM([ReportCount]))
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
All Replies
Re: Average
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-26-2018 05:28 AM
So, maybe create a new column in your fact table:
TotalTime = [ReportCount] * [AverageTime]
Then create a measure like:
MyAverage = DIVIDE(SUM([TotalTime]),SUM([ReportCount]))
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: Average
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-26-2018 05:35 AM
Excellent, thank you.