Hello everyone, I am using Schema-Workbench to create a Mondrian Schema. In order to study this schema I am using the Saiku Analytics plugin for Pentaho.
My problem comes out when I try to create a Calculated Member that shows the percentage (%), since for calculating this percentage I don’t want to consider Null values from my database.
Considering the next fact table (StOrder):

ORDER_ID GenderNid
1 M
2 F
3 M
4 F
5 F
6 M
7 Null
8 Null
9 F
10 M
11 F
12 Null
13 M
14 F
15 F




And my dimension table GENDER (StGender):
Nid Desc_Gender
M Male
F Female

I create the Measure “GENDER_COUNT” and the Dimension “GENDER” so if I calculate the number of orders per Gender I obtain the next result:

GENDER ORDER_COUNT
Male 5
Female 7

My goal is to calculate the percent of Orders of the total orders that are not null for the dimension I am using (Gender). In this case the total amount of orders would be 12 (15 – 3(null values of GenderNid)). With the next result:
GENDER ORDER_COUNT % ORDERS
Male 5 41,67
Female 7 58,33

The only result I’ve been able to obtain until now is to calculate the percent of orders of the total number of orders, considering null values (15). With the next result:

GENDER ORDER_COUNT % ORDERS
Male 5 33,33
Female 7 46,67

What I am trying to do then is to create another Calculated Member (TotalOrders_NotNull) that counts the total number of orders excluding the null values for the dimension used, but I haven’t find the MDX formula yet.
I really appreciate any help
My cube would be like this:Ejemplo_dudaPorcentaje.xml