none
How to find the Average in a Calculated Member with filtered Values? RRS feed

  • 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 gbobj Tuesday, July 23, 2013 12:49 PM Corrections
    Tuesday, July 23, 2013 12:31 PM

Answers

  • 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 Duane Dicks Tuesday, July 23, 2013 8:16 PM
    • Marked as answer by gbobj 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 Duane Dicks Tuesday, July 23, 2013 8:16 PM
    • Marked as answer by gbobj 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.


    Please, mark this post as Answer if this helps you to solve your question/problem.
    Alan Koo | "Microsoft Business Intelligence and more..." http://www.alankoo.com

    Tuesday, July 23, 2013 8:35 PM