cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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.

daily sum.PNG

 

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

quarterley sum.PNG

 

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

data table.PNG

 

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

drill down button.PNG

 

Hoping you can help. 🙂

 

2 REPLIES 2
Highlighted
Community Support
Community Support

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

Hi @chrisdo ,

 

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

6.PNG

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

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

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

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. 

yearly average.PNG

 

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors