Calculating averages using a filter

[RichFrederick]

Hi all, 

Hit a roadblock, trying to find the overall average for "rate" that occurs in multiple columns. Some of the data has "0" and I want to exclude them. How do I apply a filter to exclude rows that have "0" in their values? 

2018 AVG RATE = AVERAGE('ALP2018 Daily Rate'[Rate])+AVERAGE('HUF2018 Rate'[Rate])+AVERAGE('KPH2018 Rate'[Rate])+AVERAGE('GROUP2018 Rate'[Rate])+AVERAGE('INDY2018 Rate'[Rate])+AVERAGE('LKE2018 Rate'[Rate])+AVERAGE('LOCAL2018 Rate'[Rate])

Thanks

[v-piga-msft]

Hi @RichFrederick,

You could refer to the formula below.

average=CALCULATE(AVERAGE('ALP2018 Daily Rate'[Rate]),FILTER('ALP2018 Daily Rate','ALP2018 Daily Rate'[Rate]<>0))

If you still need help, please share some data sample which could reproduce your scenario and your expected output, so that we can help further investigate on it?

Best Regards,

Cherry

[RichFrederick]

Thank you!! I got that to work for one, but how do I apply the <>0 filter to the DAX below?  I've tried writing the filter after each I get the following: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Overall Occupancy = CALCULATE(AVERAGE('CHI2018 Occ'[Occupancy]),FILTER('CHI2018 Occ','CHI2018 Occ'[Occupancy]<>0)+AVERAGE('CIN2018 Occ'[Occupancy]),FILTER('CIN2018 Occ','CIN2018 Occ'[Occupancy]<>0)+AVERAGE('DET2018 Occ'[Occupancy]),FILTER('DET2018 Occ','DET2018 Occ'[Occupancy]<>0)+AVERAGE('GROUP2018 Occ'[Occupancy]),FILTER('GROUP2018 Occ','GROUP2018 Occ'[Occupancy]<>0)+AVERAGE('IOWA2018 Occ'[Occupancy]),FILTER('IOWA2018 Occ','IOWA2018 Occ'[Occupancy]<>0)+AVERAGE('MIN2018 Occ'[Occupancy]),FILTER('MIN2018 Occ','MIN2018 Occ'[Occupancy]<>0)+AVERAGE('REMOTE2018 Occ'[Occupancy]),FILTER('REMOTE2018 Occ','REMOTE2018 Occ'[Occupancy]<>0))

[v-piga-msft · Accepted solution]

Hi @RichFrederick,

Try this formula below.

Overall Occupancy =
CALCULATE (
    AVERAGE ( 'CHI2018 Occ'[Occupancy] ),
    FILTER ( 'CHI2018 Occ', 'CHI2018 Occ'[Occupancy] <> 0 )
)
    + CALCULATE (
        AVERAGE ( 'DET2018 Occ'[Occupancy] ),
        FILTER ( 'DET2018 Occ', 'DET2018 Occ'[Occupancy] <> 0 )
    )
    + CALCULATE (
        AVERAGE ( 'GROUP2018 Occ'[Occupancy] ),
        FILTER ( 'GROUP2018 Occ', 'GROUP2018 Occ'[Occupancy] <> 0 )
    )
    + CALCULATE (
        AVERAGE ( 'IOWA2018 Occ'[Occupancy] ),
        FILTER ( 'IOWA2018 Occ', 'IOWA2018 Occ'[Occupancy] <> 0 )
    )
    + CALCULATE (
        AVERAGE ( 'MIN2018 Occ'[Occupancy] ),
        FILTER ( 'MIN2018 Occ', 'MIN2018 Occ'[Occupancy] <> 0 )
    )
    + CALCULATE (
        AVERAGE ( 'REMOTE2018 Occ'[Occupancy] ),
        FILTER ( 'REMOTE2018 Occ', 'REMOTE2018 Occ'[Occupancy] <> 0 )
    )

Best Regards,

Cherry