Post reply

Calculated Member question

  • January 30, 2007 at 7:39 pm

    #115433

    Here's the scenario.

    I have a fact table called EmployeeCompensation. Each row in this table represents all of the compensation information for exactly one employee. One of the fields in it is called Bonus, and I have a measure based on this field. There are several dimensions linked to this fact table, but only one of them - Status - is relevant to the problem. The Status dimension has one relevant attribute, Current.

    What I'm trying to do is create a calculated measure that represents an average bonus - but it's not a simple calculation. What I need is to divide the total bonus (easy enough) by the number of rows in the EmployeeCompensation table where the Bonus value is greater than zero and the row is linked to the Active member of the Current attribute of the Status dimension (less easy). The reason for this is that the average value needs to only represent 'Active' employees who actually received a bonus.

    I've tried to figure out how to do this with MDX, but I'm stumped. How do I count only the rows that meet the right criteria to get the correct divisor for my average?

    Thanks

    Matt

  • February 2, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • July 31, 2008 at 5:24 am

    #851171

    Hi there.

    U probably solved the problem already. But here it goes:

    If u have a distinct count of employees u can do this:

    Aggregate([employees].[ALL],[Measures].[sum bonus value]) /

    aggregate([Status].[Active],[Measures].[distinct count of employees])

    If u don't have a distinct count of employees then:

    Aggregate([employees].[ALL],[Measures].[sum bonus value]) /

    aggregate([Status].[Active],COUNT( [Employees]., EXCLUDEEMPTY))

    This will calculate based on the members of dimensions u select at the time (the active set for each dimension). U also should include a iif to check if the count is non zero so it doesn't give division by zero (Syntax: IIF( «Logical Expression», «object», «object» )).

    I'm hoping I could still help u. But it stays for others as well.

    see u:cool:

    SkullKiller

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply