Forum Discussion

RichFrederick's avatar
RichFrederick
Frequent Visitor
8 years ago
Solved

Calculating averages using a filter

  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 ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    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

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

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's avatar
RichFrederick
Frequent Visitor
8 years ago

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's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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