11 Replies Latest reply on Oct 11, 2017 4:11 PM by Hari Ankem

# Calculating Mean and Median from "Number of Rows"

Hi everyone!

I have an excel file with around 30,000 rows of data, starting from 7/1/2012 to 10/8/2017. Each data entry includes an "arrival date&time" field, which has a date and time inside. I am trying to visually display the mean and median by weekday and by hour of the day, depending on a filter that I have created that corresponds to the fiscal year. Now, when I try to do that, I just get a bunch of 1's, and not the actual mean and median per day and hour. Does anyone know why this is happening and how to possibly remedy this?

• ###### 1. Re: Calculating Mean and Median from "Number of Rows"

You need to provide a packaged workbook so that someone can provide a relevant solution.

• ###### 2. Re: Calculating Mean and Median from "Number of Rows"

The file is attached. Any help is appreciated.

• ###### 3. Re: Calculating Mean and Median from "Number of Rows"

You need to convert the date-time to a numeric value and do the required operations on it as shown below:

Refer attached workbook. Hope this helps.

• ###### 4. Re: Calculating Mean and Median from "Number of Rows"

Perhaps I should have been a bit more clear. What I'm looking for is a visual like this:

And this:

But instead of the count of the number of records (or the sum), I'm looking for mean and median. So, essentially, there will be 6 different dashboards:

1. Showing TOTAL Cases by Weekday (done)

2. Showing TOTAL Cases by Hour (done).

3. Showing MEAN Cases by Weekday.

4. Showing MEAN Cases by Hour.

5. Showing MEDIAN Cases by Weekday.

6. Showing MEDIAN Cases by Hour.

As can be seen, I need help with 3-6. You were on the right track, but I probably wasn't clear enough on what I wanted. Hope this helps you (with helping me!)

• ###### 5. Re: Calculating Mean and Median from "Number of Rows"

OK. Here is your data filtered for a single year,month, weekday and department. Let me know what is the mean and median value you are expecting here?

• ###### 6. Re: Calculating Mean and Median from "Number of Rows"

Yes, so that is in May 2015. The analysis only requires July-October for 2014-2017.

The more recent data like Sunday 10/8/2017 and Sunday 10/1/2017 contains 34 and 27 cases, respectively, for just CES-MH (I need both departments).

Would there be a way to do what I need graphically, like in the pictures I posted above?

• ###### 7. Re: Calculating Mean and Median from "Number of Rows"

OK. So, how do you want to compute the mean? Is it a mean of the department values? You must have thought something about it.

• ###### 8. Re: Calculating Mean and Median from "Number of Rows"

What you are describing is taking the sum of records per day, then taking the mean/median of those aggregated values. Taking an aggregate of an aggregate warrants either a table calculation or an LOD calculation. The LOD calculation could look like:

{ INCLUDE DATEPART('weekday', [Ed Arrival Dttm]):SUM([Number of Records])}

and you would drop that into your view, setting aggregation to either avg or median.

What this does is pre-aggregates at weekday level, repecting any additional filters or dimensions you have on your view, and then averages/medians that set.

When you were taking the avd/median of [Number of Records] it was calculating at the row-level, and every row contains 1 in that field. So the Avg or median of a bunch of 1s is always 1.

• ###### 9. Re: Calculating Mean and Median from "Number of Rows"

Hey Justin,

I entered that expression and it still gives me the same problem I'm currently having.

• ###### 10. Re: Calculating Mean and Median from "Number of Rows"

sorry, sample calculation was for weekday, irrespective of date:

{ INCLUDE DATETRUNC('day', [Ed Arrival Dttm]) : SUM([Number of Records])}

• ###### 11. Re: Calculating Mean and Median from "Number of Rows"

Here you go. You have all the charts now.

Refer attached workbook.