monthly average of daily sums

[chrismiller]

So I have some visuals. Such as Barcharts showing quantity by date- which can be drilled down from month to day

currently it works like this:

the dailey sum is base on quantity across multiple sites and on the monthly veiw it is based on the average of all sites for all days of the month.  The formula I used to switch is:

AVG/SUM HHPU = IF(ISFILTERED(daily_quantity[DailyQuantity_Date].[Day]),SUM(daily_quantity[Quantity]),AVERAGE(daily_quantity[Quantity]))

However what I actually need is something like this:

 what this will do is give me my daily sum (which I currently have) however rather than month average of all sites... I need monthly average of all those daily sums.

I was playing around with the group by function in query editor.. which works great. however it gives me just the daily sum across all sites and districts etc... but there are some page level slicers and filters that I would still need use. ie quantity by date is not the lowest grain.

[v-chuncz-msft · Accepted solution]

@chrismiller,

You may refer to the following measure.

Measure =
AVERAGEX (
    SUMMARIZE ( daily_quantity, daily_quantity[DailyQuantity_Date] ),
    CALCULATE ( SUM ( daily_quantity[Quantity] ) )
)