Hi,
I have the following data that shows car counts by hour filtered by day of week (6, saturday). What I would like to do is coalesce this into a single average by hour for Saturday, so I can overlay live data for comparison. Because the data arrives in bursts during an hour it the measure Currentcount is by default summed, which usually serves perfectly well.

In this case I want to sum up Currentcount at the < hourly level, but average across days (ie average of 11-12 am over {today, last saturday, previous saturday......})

Name:  all saturdays.jpg
Views: 179
Size:  18.8 KB

Generated with this:

WITH
SET [~FILTER] AS
{[Device.SiteID].[55]}
SET [~Date_Date.YWD_Week of year] AS
Exists({[Date.YWD].[Week of year].Members}, [~Date_Date.YWD_Day of week])
SET [~Date_Date.YWD_Day of week] AS
{[Date.YWD].[2017].[19].[6], [Date.YWD].[2017].[20].[6], [Date.YWD].[2017].[21].[6], [Date.YWD].[2017].[22].[6], [Date.YWD].[2017].[23].[6], [Date.YWD].[2017].[24].[6], [Date.YWD].[2017].[25].[6]}
SET [~COLUMNS] AS
Hierarchize({[~Date_Date.YWD_Week of year], [~Date_Date.YWD_Day of week]})
SET [~ROWS] AS
{[Time.HMS].[Hour].Members}
SELECT
NON EMPTY CrossJoin([~COLUMNS], {[Measures].[Currentcount]}) ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [Radar_Analysis3]
WHERE [~FILTER]

Closest I have gotten is this:
WITH
MEMBER [Measures].[avgHourLocal2] AS
Avg(Descendants([Time.HMS].[Hour].Currentmember), [Measures].[Currentcount])
SET [~ROWS] AS
{[Time.HMS].[Hour].Members}
SELECT
NON EMPTY {[Measures].[avgHourLocal2]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [Radar_Analysis3]
WHERE CrossJoin({[Device.SiteID].[55]}, {[Dim date.Day of week].[6]})

Which looks to give the correct shape, but is about 1/3 of the magnitude (probably because I think this site reports 3 times an hour). So its averaging all currentcounts at every level, where I need it to sum when below hourly level, and average in every other dimension