topic Re: Daily average of daily sum - on date drill down (running daily average?) in Desktop

Daily average of daily sum - on date drill down (running daily average?)

Anonymous — Fri, 22 May 2020 09:42:36 GMT

I have counts of items per day. I want to sum the items to get a total for each day, then show a time series chart showing the daily AVERAGE across time - no matter what time period is on the drill down.

I use a date table for the date hierarchy.

The average daily sum is about 30-40, but when viewing year, it shows the total of all items across every day in the year - about 700.

For quarter > month > week, naturally the number drops until once you've drilled down to day you get the correct daily sum.

When viewing daily (drill down in the chart all the way to by day) you see the daily sums - about 30-40.

When drilled 'up' at quarter I want to still see the daily average, but actually I see the quarterley sum - about 100.

My data is structured like this. Blocked out in red is my item count - for a particular day:

This all happens when I click the chart drill-down button:

Hoping you can help. 🙂

Re: Daily average of daily sum - on date drill down (running daily average?)

v-xuding-msft — Mon, 25 May 2020 08:44:03 GMT

Hi Anonymous ,

Do you want to show the average values whenever drill down the date hierarchy? Do you try the average in fields?

If I misunderstand, please share the expected result to us. We will understand more clearly.

Re: Daily average of daily sum - on date drill down (running daily average?)

Anonymous — Mon, 25 May 2020 09:08:45 GMT

Yes I'd like to see the average daily value - however this needs to be the sum of the different value types for each day.

In my data screen shot above you'll see each day has multiple data entries for different locations. I need these to be summed for the day, but averaged to give the daily average (whether looking yearly, monthly, weekly, etc).

I have tried using average (as per your suggestion) it averaged all the values for that day, then averaged all the days across the time period.

My data values typically range between 0 and 20 with a lot of zeros - so the average comes out at below zero. But if you sum all records for the day first, then take the daily average across time it should be around 30-40.

So I assumed creating a measure like this

Sum of vacant beds = sum('Bed Counts'[Vacant Beds])

would do the summing across the day, but this doesn't change anything. I think I am missing something in this measure that sums the whole day then avarages per day.

Thanks for your help.