Search code examples
powerbisumdaxaveragemeasure

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])

    enter image description here

    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]
)

    enter image description here