Aggregating from different level

[#1]

Hi all,

I have an issue with a Sum of diferent values for a dimension that is a lower level respect fact table.

My fact table don't include a dimension that I need for work with:

FACT TABLE
Id_Person	Age	Gender	Affected
1	3	Male	1
2	1	Female	1
3	14	Male	1
4	33	Male	1
5	20	Female	1
6	45	Male	1
7	67	Male	1
8	74	Female	1

Dimension:

DIMENSION TABLE
Dimension	Sum (Affected)	Condition
Dim1	3	<15
Dim2	2	>15 & Female
Dim3	2	>65
Dim4	8	ALL
Dim5	8	ALL
…	…	…
Total	8

Although 'Dimension' is not included in Fact Table, it is possible work with it joining this dimension by different conditions like Age and Gender. I need obtain something like this, but additionally I need work with multiple values for this dimension:

Expressions
Selected Dimension	Sum(Affected)
Dim1;Dim2	5
Dim2;Dim3	3
Dim1;Dim2;Dim3	6
Dim1;Dim2;Dim4	8
Nothing in Selector	8

I try to get this result:

Expedted
Dimension	Cost	Affected people
Total	6000	8
Dim1	1000	3
Dim2	1000	2
Dim3	2000	2
Dim4	1500	8
Dim5	500	8

with a Set Analisys like:

Sum({<$(PARAM Excluded Dimensions)  >} $(PARAM Conditions) FT_AFFECTED_PEOPLE))

where:

$(PARAM Excluded Dimensions) are Dimensions including 'Dimension'. I need this clause for obteining the correct value.

$(PARAM Conditions) is a CASE expression that add filtering condition for fact.

FT_AFFECTED_PEOPLE is a simple registrer count from Fact Table.

With this solution I can obtain the Total Count of affected people but don't get the value of each dimension:

Solution1
Dimension	Cost	Affected people
Total	6000	8
Dim1	1000	0
Dim2	1000	0
Dim3	2000	0
Dim4	1500	0
Dim5	500	0

This solution works fine for a text box with a totalizer, but in a Pivot Table with 'Dimension' is not a fine solution.

Other solution that I tried are Aggregator by 'Dimension' and modify script for get a field with a value for each value of 'Dimension', but  I can't get a correct solution.

What can I do for get correct solution avoiding cross join between Dimension and Fact Table?

Thank you in advance.

[Gysbert_Wassenaar · Accepted solution]

Perhaps the attached example helps. If not, please explain why.

[#3]

Many thaks Gysbert!

It is not a perfect solution, because Totals do not work correctly, but for me it is fine.

Now, I have a problem with my data because Fact Table is concatenating some local tables and expression don't work fine because a table has checked dimension. Anyway, it's another issue.