topic Re: Sum of averages if no filter in DAX Commands and Tips

Sum of averages if no filter

Metricbits — Sat, 29 Aug 2020 10:39:31 GMT

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

Re: Sum of averages if no filter

Greg_Deckler — Sat, 29 Aug 2020 14:21:14 GMT

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.

Re: Sum of averages if no filter

Metricbits — Sun, 30 Aug 2020 02:42:58 GMT

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

Re: Sum of averages if no filter

Metricbits — Sun, 30 Aug 2020 03:18:57 GMT

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

Re: Sum of averages if no filter

daxer-almighty — Sun, 30 Aug 2020 06:59:30 GMT

[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:

Houses is a dimension. Each row is a different house.
Calendar is a date dimension.
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.

Re: Sum of averages if no filter

Metricbits — Sun, 30 Aug 2020 08:07:21 GMT

Thank you, this worked like magic