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

     

     

     

     

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

    • 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