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

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

3 Replies

  • 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