Calculate the percentage based on the summs of hourly sales and their daily and weekly averages.

[tarasic]

I have a dataset with the following columns:
Date
Time
units sold
week#
hour

I need to calculate the difference between the hourly sales and their total daily and weekly averages.

Where:
1 - SUM of sales by hours
2 - daily AVERAGES of these SUMS by hours
3 - AVERAGE of ALL hourly sales in a week

4 - result of division of every 1 (SUM of sales by hours) by 3 (total AVERAGE of ALL hourly sales in a week)

I can make the first step by this measure:

AvgPerDaySumByHour =

VAR _DailySoldByHour =

    SUMMARIZECOLUMNS(

        DimDate[Week+Day+Short],

        data[Time (hours)],

        "TotalSold", SUM(data[units sold])

    )

RETURN

AVERAGEX(_DailySoldByHour, [TotalSold])

And get the same table in Power BI.

I don't understand how to get my "total averages" 3 from this table, to calculate %. 

You can find the sample PBIX here.

[tarasic]

Thanks!!!

[lbendlin · Accepted solution]

Here is how you calculate #3

AvgPerWeek = 
VAR _DailySoldByHour = 
    SUMMARIZECOLUMNS(
        DimDate[Week+Day+Short], 
        data[Time (hours)],
    allexcept(DimDate,DimDate[WeekNumber]),
    REMOVEFILTERS(data[Time (hours)]),
        "TotalSold", SUM(data[units sold])
    )
RETURN
AVERAGEX(_DailySoldByHour, [TotalSold])

and then you can do the percentage accordingly.