Need help to create a complicate MDX

Hello all,

I need to create a complicated calculated member that a similar as an SQL statement “select where in”. So there are two dimensions errors and products. Each product is assigned to an error or has no error. Each product has also some amount. For example, say we have errors E1, E2, E3 and N/A is for no errors and we have also three products P1, P2 and P3. For instance we have following situation:

Product Error Amount
P1 E1 10
P2 E1 20
P3 E2 30
P1 E2 10
P1 E3 10
P2 E3 20
P2 N/A 20
P2 N/A 20
P1 N/A 10

And I need to see following in the cube

Error Amount Failed Amount total
E1 30 120
E2 40 70
E3 30 120
N/A 50 120

Amount failed is amount for each error. So for E1 these are products P1 + P2 -> 10 + 20 = 30. For E2 -> P3 + P1 -> 30 + 10 = 40. For E3 -> P1 + P2 -> 10 + 20 = 30. For N/A -> P2 + P2 + P1 -> 20 + 20 +10 = 50. This is simply- My problem is Amount total. Amount total is a sum of amounts of all products that are exist on the current level. For example to E1 are following products assigned: P1 and P2. So I need to find all amounts for P1 and P2 on all levels. These are 10 (P1 E1) + 20 (P2 E1) + 10 (P1 E2) + 10 (P1 E3) + 20 (P2 E3) + 20 (P2 N/A) + 20 (P2 N/A) + 10 (P1 N/A) = 120. And this is my problem! If I would write a SQL I would do something like this: select sum(amount) from cube where product in (select product from cube where error = ‘E1’) and so on for E2 and E3. But how I can do the same with MDX?

Any help will be appreciating.
Thanks and best regards,
Andrej