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

     

     

     

     

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

Rebender's avatar
Rebender
Frequent Visitor
7 years ago

This is working great except I have one more question/problem.....  I have slicers on my report that the measure seems to be ignoring.   Do I have to do something else in order for the measure to use the slicers?

 

thanks again!!!

Renee

  • Ronald123's avatar
    Ronald123
    Resolver III
    7 years ago

    Hello Rebender,

     

    If the date field in the slicer is from an another table, you must make a connection between the differt tables.
    Otherwise, you must give more information because a slicer with a data field from the table of your'e measures must work,

     

    Greets,

     

    Ronald

     

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

    hi, Rebender

    You should check these slicers if had created the relationship with this table and the cross filter direction if is both.

    otherwise it should can be affected by slicer.

     

    Best Regards,

    Lin