# How to find the Average in a Calculated Member with filtered Values? • ### Question

• Hi to you all,

I currently trying to Implement a Calculated Member (SSRS or SSAS ) which would Show me the Average Values per Customer, i hereby Need to devide the Sum of Values from all Projects, and devide it by the amount of Projects that don't have 0 as a Value.

A similiar table of Values like the one I am using is shown below.

Customer   | Project   |  Value      |

Customer1  | Project1   | 124.24...  |

Customer1  | Project2   |     0.00     |

Customer1  | Project3   | 242.221... |

Customer1  | Project3   | 72.221...  |

now what i tried to do in my Calculated Member is the following (i tried this in both SSRS and SSAS)

`Sum([Measures].[Value]) /Count(Filter([Order].[Project], [Measures].[Value] > 0))`

my hope was to filter out the Amount of Projects that have a Value of Zero, and divide the sum of Values only by the Amount of Projects that have a Value of <> 0.
the Problem is, when i get to the browser so I try my Calculated Member out the Count Value of Projects (relative to the ex. above) is 1 instead of 3,
and the Average value I get is : (124.24 + 0 + 242.221 + 72.221) / 1
as opposed to (124.24 + 0 + 242.221 + 72.221) / 3  --(not devide by 4 because 1 project has the value of 0, and that's the one I want to exclude from the Count that I make).

All Help is very Appreciated,
i thank you all in Advance.

• Edited by Tuesday, July 23, 2013 12:49 PM Corrections
Tuesday, July 23, 2013 12:31 PM

• I think why you are only getting 1 instead of 3 is because your filter is only looking at the all member and it should be looking at it's children.

Something like this should do it:

`Sum([Measures].[Value]) /Count(Filter([Order].[Project].level(0).Children, [Measures].[Value] > 0))`

My Blog

• Proposed as answer by Tuesday, July 23, 2013 8:16 PM
• Marked as answer by Wednesday, July 24, 2013 8:32 AM
Tuesday, July 23, 2013 8:15 PM

### All replies

• I think why you are only getting 1 instead of 3 is because your filter is only looking at the all member and it should be looking at it's children.

Something like this should do it:

`Sum([Measures].[Value]) /Count(Filter([Order].[Project].level(0).Children, [Measures].[Value] > 0))`

My Blog

• Proposed as answer by Tuesday, July 23, 2013 8:16 PM
• Marked as answer by Wednesday, July 24, 2013 8:32 AM
Tuesday, July 23, 2013 8:15 PM
• Try replacing:

Count(Filter([Order].[Project], [Measures].[Value] > 0))

by

Count(Filter([Order].[Project].[Project], [Measures].[Value] > 0))

In this way you will be counting all the exiting projects and not only the current one.

HTH.