MDX Calculation for summing the product of two measures and multiplying that by the last children of two other metrics

Question

Here is a snip of my cube's dimension usage:

In "TD Measures" I have

[A] "Billable Client Hours Current".

In "Personnel Measures" I have

[B] "FTE Count" (an employee's amount they are working like .5 for people who work 20 hours per week)
[C] "Forecast FY End" Forecast for the end of the fiscal year
[D] "Forecast FYTD" Forecast for the current period to date in the fiscal year.

The calculation I need to do at a leaf level is:

[A][B][C]/[D]

The issue is that [A] has more dimensionality that [B], [C], and [D].

So If this is my data:

The calc I need to do is:

((15*.05)+(5*1)) //sum of hours in a period multiplied by that month's FTE

X 2000/300 //last child of FCFYE divided by last child of FCFYTD in the currently selected set.

This calc is likely to change a little over the next week or two, but this is the main concept of what they are looking to do. Any help in writing the MDX for this to create a calculated member in my cube is greatly appreciated. Sorry if I've left out any key info--I'm quite the MDX noob.

Andrew Bilbrey Andrew Bilbrey · Accepted Answer · 2017-02-16T15:11:16

Through digging through Chris Webb's and Mosha's posts as well as working with some guys on the MSDN forums I have worked out a solution to this below. Ultimately, I worked out with the business that we didn't need to interrogate the underlying rows to discern if they were active or not since the addition of the root functionality eliminated the need as long as they filtered on it from DimMiscellaneous. But for the sake of helping someone with a similar problem here is the full MDX below.

//Only evaluate if they are active
CREATE HIDDEN UtilizedFTESummator;    
[Measures].[UtilizedFTESummator] = Iif([Measures].[Is Active For Utilization Value] > 0,[Measures].[Period FTE],NULL);    
NON_EMPTY_BEHAVIOR([Measures].[UtilizedFTESummator]) = [Measures].[Is Active For Utilization Value];  

//only include this measure if the underlying employee has values in their underlying data for active in utilization
CREATE MEMBER CURRENTCUBE.[Measures].[FTE Active Utilization]
AS
SUM
    (   
    EXISTING [Historical Personnel].[Employee Id].[Employee Id], 
    [Measures].[UtilizedFTESummator]
    ),VISIBLE=0;  

//Show weighted FTE by workdays 
CREATE MEMBER CURRENTCUBE.[Measures].[FTE MTD Active Utilization]
 AS SUM
(
    DESCENDANTS([Historical Personnel].[Employee Id].CURRENTMEMBER,[Historical Personnel].[Employee Id].[Employee Id]),
    (
    DIVIDE
        ( 
        SUM
            (   
            DESCENDANTS([Period].[Fiscal Period].CURRENTMEMBER,[Period].[Fiscal Period].[Fiscal Period]),
                    [Measures].[FTE Active Utilization]*[Measures].[Work Days In Month]
            )
        ,SUM(ROOT([Historical Personnel].[employee id].currentmember),[Measures].[Work Days In Month])
        ,0
        )
    )
);

//Use Weighted FTE for calculating the weighted value for each person (all periods aggregated) 
//Forecast Billable End Of Year has default aggregation of last child
CREATE MEMBER CURRENTCUBE.[Measures].[Annualized CBH Projected]
 AS DIVIDE 
(
    SUM
        (
        DESCENDANTS([Historical Personnel].[Employee Id].CURRENTMEMBER,[Historical Personnel].[Employee Id].[Employee Id]),
        [Measures].[Billable Client Hours Current] *
        (
        DIVIDE
            (
            [Measures].[Forecast Billable End Of Year]
            ,       
            [Measures].[Forecast Billable FTE]
            ,0
            )
        )
        *[Measures].[FTE MTD Active Utilization]
    )
    ,[Measures].[FTE MTD Active Utilization]
    ,0
    );

Simplified answer with the users filtering on the Is Active For Utilization flag is as follows:

//Weight FTE by employee calculating FTE * workdays for each period and then dividing by the sum of days irrespective of filters on historical personnel or miscellaneous
CREATE MEMBER CURRENTCUBE.[Measures].[FTE MTD] 

 AS SUM
(
    DESCENDANTS([Historical Personnel].[Employee Id].CURRENTMEMBER,[Historical Personnel].[Employee Id].[Employee Id]),
    (
    DIVIDE
    (
    SUM
        (
        DESCENDANTS([Period].[Fiscal Period].CURRENTMEMBER,[Period].[Fiscal Period].[Fiscal Period]),[Measures].[Period FTE]*[Measures].[Work Days In Month]
        ) 
        ,
        SUM((ROOT([Historical Personnel].[employee id].currentmember),ROOT([Miscellaneous]),[Measures].[Work Days In Month]))
    ,0
    )
    )
);


//Weight by FTE with default agg for Forecast EOY being last child.
CREATE MEMBER CURRENTCUBE.[Measures].[Annualized CBH Projected]
 AS DIVIDE 
(
    SUM
        (
        DESCENDANTS([Historical Personnel].[Employee Id].CURRENTMEMBER,[Historical Personnel].[Employee Id].[Employee Id]),
        [Measures].[Billable Client Hours Current] *
        (
        DIVIDE
            (
            [Measures].[Forecast Billable End Of Year]
            ,       
            [Measures].[Forecast Billable FTE]
            ,0
            )
        )
        *[Measures].[FTE MTD]
    )
    ,[Measures].[FTE MTD]
    ,0
    );

MDX Calculation for summing the product of two measures and multiplying that by the last children of two other metrics

1 Answers