DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

[freedomfee]

Hello,

I have a DB of professors and informatikon related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:

prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070------------------- 90 --------------------------2----------------2
1111------------------- 0 ---------------------------1----------------0
1137------------------- 0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721------------------- 504--------------------------7----------------7
2661------------------- 85 --------------------------5----------------5
...-------------------- ...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956------------------- 50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

 

[NorthStarDA]

you are so in the wrong forum, try asking in the forum corresponding to the database that you are using.

=========================================
Don't sweat the petty things and don't pet the sweaty things.