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

     

     

     

     

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