MDX: Subtotal and distinct count
Hello,
I would like to create a calculated member which count sheet per Employee and display in the total field the sum per Product Line.
The calcul need to be done on weekly basis.
The cube browser should be like that:
W9 W10 W11 W12 W13
Product Line Employee
PL1
Employee1 1 1 1 1
Employee2 1 2 1
Employee3 2 1
Employee4 1 1
.......
SubTotal 4 2 3 2 2
PL2
PL3
.....
So far I used the following MDX for the calculated member:
sum
(
existing [Dim Time].[Calendar].[Week],
[Measures].[# T-E Sheet Distinct])
The number of sheet per employee is good but the subtotal/total is false. Unfortunately I really need the subtotal/total information...
Do you know what I have to change in the MDX code in order to have also the sum per Product Line?
Any help is welcome...
Thanks!
Guillaume
Hello Bryan,
Thank you for your reply.
The measure [# T-E Sheet Distinct] is a distinct count and the aggregation function is set as a distinct count. The aim of this measure is to count distinct Time Sheet. However this count makes sense only at week level because employees enter their Time Sheet at week level. It means a distinct count of Time Sheet at month level is false.
That's why I wanted to create a calculated member which keep the distinct count per employee per week and sum for level above like month or Reporting Line.
So far with the MDX I used above the detail is fine but not the total which is false because it doesn't sum the distinct count:
W9
Employee 1 5 different Time Sheet
Employee 2 3 different Time Sheet -> OK
Employee 3 5 different Time Sheet
================================
TOTAL 5 different Time Sheet -> I have this TOTAL but I do not want that.
TOTAL 13 different Time Sheet -> I'm not able to have this TOTAL
Feel free to ask me if I have not been enough clear...
Thanks,
Guillaume
I was able to set this up with the Adventure Works OLAP cube. It takes a distinct count measure, Customer Count, and performs a SUM of children for a given level (in the Sum Customer Count calculated member). At the lowest level, Date in this case, there are no children so Sum Customer Count returns NULL.
To correct this, I calculate an Adjusted Customer Count which uses the basic Customer Count measure (DISTINCT COUNT) if the value is empty. What I should have done is make the substitution only after I verified there were no children. Still, this is a good starting point for you.
Code Snippet
withmember [Measures].[Sum Customer Count] as
SUM( [Delivery Date].[Calendar].CurrentMember.Children,
[Measures].[Customer Count]
)
member [Measures].[Adjusted Customer Count] as
IIF( ISEMPTY([Measures].[Sum Customer Count]), [Measures].[Customer Count],
[Measures].[Sum Customer Count]
)
select
{[Measures].[Customer Count],
[Measures].[Sum Customer Count],
[Measures].[Adjusted Customer Count]}
on
0, EXISTS( [Delivery Date].[Calendar].
Members
, [Delivery Date].[Month].[April 2004]
)
on
1 from [Adventure Works]
B.
