Sum of averages if no filter

[Metricbits]

Hello,

I have a table of data that looks like this

House	ResidentNumbersMTD	Period
GR	9	30/6/2020
BR	7	30/6/2020
AL	7.8	30/6/2020
VS	8	30/6/2020
RB	5.5	30/6/2020
CR	7	30/6/2020
CL	7.5	30/6/2020
FL	9.7	30/6/2020
EL	8.7	30/6/2020
IR	6.3	30/6/2020
CS	1.9	30/6/2020
KL	5.3	31/5/2020
GR	9	31/5/2020
BR	7	31/5/2020
AL	8	31/5/2020
VS	8	31/5/2020
RB	4	31/5/2020
CR	7	31/5/2020
CL	8	31/5/2020
FL	9.5	31/5/2020
EL	9	31/5/2020
IR	7.3	31/5/2020

I want to calculate the YTD average Number of residents. When house filter applied, it should be just the average number of residents for this house, however, when no house filter applied, I want Power Bi to calculate the sum of averages for all houses.

The formula I tried is:

YTD_No of Residents (MTD) =
VAR AvgMonth =
    CALCULATE (
        AVERAGEX ( Manual_Data, [No of Residents (MTD)] ),
        ALL ( 'Calendar' ),
        DATESYTD ( 'Calendar'[Date], "06-30" )
    )
VAR SumTable =
    SUMMARIZE (
        Manual_Data,
        Manual_Data[House],
        "SumTableAvg",
            CALCULATE (
                AVERAGE ( Manual_Data[ResidentNumbersMTD] ),
                ALL ( Manual_Data[Period] ),
                DATESYTD ( Manual_Data[Period], "30-6" )
            )
    )
RETURN
    IF (
        HASONEFILTER ( Houses[ShortName] ),
        AvgMonth,
        SUMX ( SumTable, [SumTableAvg] )
    )

It works ok when house filter is applied, however, when no house is selected, I get a total average for one month only (that is selected in another slicer).

Please help

[Metricbits]

Another perspective on the same issue,

Here is the table I have got bu using below formula. Line numbers are correct, but the total is wrong. It should be 83.31, not 88.46. Total must be a sum of lines

Formula I use is

xxx =
VAR one =
    SUMMARIZE (
        Manual_Data,
        Manual_Data[House],
        "SumTableAvg",
            CALCULATE (
                AVERAGE ( Manual_Data[ResidentNumbersMTD] ),
                ALL ( Manual_Data ),
                DATESYTD ( 'Calendar'[Date], "06-30" )
            )
    )
VAR two =
    SUMX ( one, [SumTableAvg] )
RETURN
    IF ( HASONEFILTER ( Houses[ShortName] ), two, SUMX ( one, [SumTableAvg] ) )

[daxer-almighty · Accepted solution]

[Your Measure] =
SUMX(
    SUMMARIZE(
        Manual_Data,
        Houses[ShortName]
    ),
    CALCULATE(
        AVERAGE( Manual_Data[ResidentNumbersMTD] ),
        // You might need to remove this ALLEXCEPT
        // if you want the measure to respond
        // to other filters.
        ALLEXCEPT( Houses[ShortName] ),
        DATESYTD(
            // Calendar must be the date table
            // marked as such in the model
            // for this to work correctly.
            'Calendar'[Date],
            "06-30"
        )            
    )
)

Please note that the above is written for a model where:

1. Houses is a dimension. Each row is a different house.
2. Calendar is a date dimension.
3. Manual_Data is a fact table that should be hidden.

All relationships are standard Dim 1-one-way-* Fact. Slicing in a star schema as the above should be done only through dimensions. Then the above will work the way it's intended to.

[Metricbits]

Thank you, this worked like magic

[Greg_Deckler]

@Metricbits This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Metricbits]

Thanks for the reply, but this doesn't solve my problem, unfortunately 😞