MDX Differing Daily\Weekly Aggregations

Question

I have a fact table that records time transactions for employees each day (numerous transactions per employee per day). We also have a daily target for each worker per day.

What I'm trying to calculate is whether for each worker per day they have met their daily target (if they have = 1 if they haven't = 0) and then at a daily\weekly\monthly level we want a count of workers who have hit their target.

So in my SSAS Cube I have created a calculated measure scoped at a worker per day using the following logic, which displays correctly when you view it at a worker per day level. However I'm unsure how I would tackle getting the day level to sum up that sub scope. In the example screen shot below (PLEASE CLICK IMAGE LINK AS I CANT CURRENTLY EMBED IMAGES) I would like the value at the Day level to be 2 (as drawn on the picture) as there are 2 people who have met their target for that day.

I'm on a bit of a steep learning curve with MDX at the moment, so this is hopefully something simple for seasoned MDXers :)

TIA

CALCULATE;           
CREATE MEMBER CURRENTCUBE.[Measures].[TimesheetTargetMet] AS NULL
,  ASSOCIATED_MEASURE_GROUP = 'Worker Targets';

SCOPE([Measures].[TimesheetTargetMet]);

        SCOPE([Ledger Date].[Financial Date].[Financial Date].MEMBERS,[Worker].[Personnel Number].[Personnel Number].MEMBERS);
               THIS = IIF([Measures].[Target Timesheet Hours] > 0 AND [Measures].[Hours] > [Measures].[Target Timesheet Hours], 1,0);
        END SCOPE;
END SCOPE;

Timesheet Target Met

Answer 1

If you wrap with the SUM function does that help?

SUM(
  (
    [Ledger Date].[Financial Date].currentmember
   ,[Worker].[Personnel Number].currentmember
  )
,IIF(
  [Measures].[Target Timesheet Hours] > 0 
    AND 
      [Measures].[Hours] > [Measures].[Target Timesheet Hours]
  ,1
  ,NULL
)
)

Answer 2

The problem with your code is the calculated measure defined in the cube script as NULL because it has no aggregation.

To resolve try adding a new named calculation in the fact table in your datasource view with the expression: NULL

In your cube structure add the named calculation column as a real measure to your fact and chose as aggregation method SUM.

SCOPE([Measures].[Your Measure]);
    SCOPE([Ledger Date].[Financial Date].[Financial Date].MEMBERS);        
        SCOPE([Worker].[Personnel Number].[Personnel Number].MEMBERS);
            THIS = IIF([Measures].[Target Timesheet Hours] > 0 AND [Measures].[Hours] > [Measures].[Target Timesheet Hours], 1,0);
        END SCOPE;
    END SCOPE;
END SCOPE;

Answer 3

My first guess is:

CALCULATE;           
CREATE MEMBER CURRENTCUBE.[Measures].[TimesheetTargetMet] AS
SUM(
    NonEmpty(
        existing [Ledger Date].[Financial Date].[Financial Date].Members * [Worker].[Personnel Number].[Personnel Number].Members,
        [Measures].[Target Timesheet Hours]
    ),
    IIF(
        [Measures].[Hours] > [Measures].[Target Timesheet Hours],
        1,
        NULL
    )
), ASSOCIATED_MEASURE_GROUP = 'Worker Targets';

However, it may appear to work slow with drill through selects.