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

     

     

     

     

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

  • 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