How to Sum Values by Column 'Date' After Averaging by Column 'Site'?

I have a standard dim table of date (consist of column: Date, Month, Year) and table of site. Also have one fact table as following:

Date	Site	Pit	Value
01-01-2024	SITE-1	PIT-A	12
01-01-2024	SITE-1	PIT-B	10
01-01-2024	SITE-1	PIT-C	02
01-02-2024	SITE-1	PIT-A	09
01-02-2024	SITE-1	PIT-B	15
01-02-2024	SITE-1	PIT-C	30
02-01-2024	SITE-1	PIT-A	04
02-01-2024	SITE-1	PIT-B	03
02-01-2024	SITE-1	PIT-C	08

I expect to create a table in PBI page as following:

Month	Site	MeasureValue
JANUARY	SITE-1	26
FEBRUARY	SITE-1	5

value of 26 and 5 is come from:

every date, we do AVERAGE for all PIT, so in date 1 January, we got AVERAGE(12,10,2)=8 and date 2 January is 18
after AVERAGING above, we SUM the average result for return MONTHLY VALUE, so January will be SUM(8,18)=26
so the same for February onward

The question is, how to build MEASURE that return that scenario? Any assistance would be highly appreciated.

Thanks

Solution

First create the average by each date :

Daily Average = AVERAGE('FactTable'[Value])

Then you need to aggregate the daily averages at the month level, in other words : you need to create a summary table using SUMMARIZE to group by each unique day within the FactTable and calculate the daily average for that day. Then with SUMX you will iterate over this summary table and sums these daily averages.

Sum of Daily Averages = 
SUMX(
    SUMMARIZE(
        'FactTable',
        'DimDate'[Date], "DailyAvg", [Daily Average]
    ),
    [DailyAvg]
)