Forum Discussion

Rebender's avatar
Rebender
Frequent Visitor
7 years ago
Solved

Calculate Average with Filter

Good afternoon,

I am trying to create an average using a filter.  In my screen shot below, you can see that under the Attribute column there are two different values.  I want to be able to sum up the Process Hours Entry to 1CR column and divide it by the count of the Attribute column where the value is 1CR_Associate_ID.  Currently when I try and just use the average function it is dividing the total by 16.  Any ideas on how to accomplish this???  Thanks in Advance!!

 

Renee

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    HI, Rebender

    You may try to this formula to create a measure as below:

    Measure = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Process Hours Entry to] ) ),
    CALCULATE (
        COUNTA ( 'Table'[Attribute] ),
        'Table'[Process Hours Entry to] <> 0
    ),
    0
)

    or use this formula to create a column

    Column = DIVIDE (
    CALCULATE ( SUM ( 'Table'[Process Hours Entry to] ),FILTER('Table','Table'[Attribute]=EARLIER('Table'[Attribute]) )),
    CALCULATE (
        COUNTA ( 'Table'[Attribute] ),FILTER('Table','Table'[Attribute]=EARLIER('Table'[Attribute])&&
        'Table'[Process Hours Entry to] <> 0)
    ),
    0
)

    Result:

    measurecolumn

    here is pbix, please try it.

    https://www.dropbox.com/s/rgvy7m8w1l15we6/Calculate%20Average%20with%20Filter.pbix?dl=0

     

    Best Regards,

    Lin

     

     

     

     

10 Replies

  • blodus's avatar
    blodus
    Advocate IV
    3 years ago

    Is there anything in Power BI that isn't ridiculously difficult to do? 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Perhaps something along the lines of:

     

    Measure = 
VAR __attribute = MAX('Table'[Attribute])
RETURN AVERAGEX(FILTER('Table',[Attribute]=__attribute),[1CR])

    Put in table visual along with Attribute.

    • Rebender's avatar
      Rebender
      Frequent Visitor
      7 years ago

      Thanks for you assistance!  Any idea why the value calculates out to 8.17?  If I add the numbers and divide by 4 I come up with 7.81....

       

  • Ronald123's avatar
    Ronald123
    Resolver III
    7 years ago

    Hello Rebender,

     

    Try this;

    AverageFilter = DIVIDE(
SUM(Table1[1CR]);
    CALCULATE(    
    COUNT(Table1[Attribute]);
    Table1[Attribute]="1CR_Associate_ID"))

    Greets,

     

    Ronald

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    HI, Rebender

    You may try to this formula to create a measure as below:

    Measure = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Process Hours Entry to] ) ),
    CALCULATE (
        COUNTA ( 'Table'[Attribute] ),
        'Table'[Process Hours Entry to] <> 0
    ),
    0
)

    or use this formula to create a column

    Column = DIVIDE (
    CALCULATE ( SUM ( 'Table'[Process Hours Entry to] ),FILTER('Table','Table'[Attribute]=EARLIER('Table'[Attribute]) )),
    CALCULATE (
        COUNTA ( 'Table'[Attribute] ),FILTER('Table','Table'[Attribute]=EARLIER('Table'[Attribute])&&
        'Table'[Process Hours Entry to] <> 0)
    ),
    0
)

    Result:

    measurecolumn

    here is pbix, please try it.

    https://www.dropbox.com/s/rgvy7m8w1l15we6/Calculate%20Average%20with%20Filter.pbix?dl=0

     

    Best Regards,

    Lin

     

     

     

     

    • Rebender's avatar
      Rebender
      Frequent Visitor
      7 years ago

      Thank you so much for your help!  I have just one more question....  How would I change the code if I want to have this divide by a distinct count of WPS?  I have two rows that have the same number so I would like to sum the 4 rows hours and then divide by a distinct count of WPS (3)...

       

      Thanks again!

      Renee

      • Ronald123's avatar
        Ronald123
        Resolver III
        7 years ago

        Helle Rebender,

         

        Try this;

        Measure2 = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Process Hours Entry to] ) );
    CALCULATE (
        DISTINCTCOUNT( ( 'Table'[WPS] ));
        'Table'[Process Hours Entry to] <> 0
    );
    0
)

        Greets,

         

        Ronald