How to change data model to do a custom matrix?

[ovonel]

I have my Fact and a Project dimension:

Dimension Project:

Proj	Discount	Level1	Level2	Level3
1	.05	xyz	xyz	xyz
2	.10	xyz	xyz	xyz
3	.10	xyz	xyz	xyz
4	.5	xyz	xyz	xyz
5	.8	xyz	xyz	xyz
6	.1	xyz	xyz	xyz

Fact:

Proj	Client	Gross Fees	Net Revenue	Expected Revenue	Diff
1	1	243	20	230,85	-210,85
2	1	52	2000	46,8	1953,2
3	1	41	10	36,9	-26,9
4	2	341	35	170,5	-135,5
5	2	134	71	26,8	44,2
6	2	34	34	30,6	3,4

Expected Revenue is a calculated column: Revenue[GrossFees] - ( RELATED(Project[Discount]) * Revenue[GrossFees] )

Diff is also a calculated column: Net Revenue - Expected Revenue

I need to achieve a matrix like:

				Diff
Client				-300
	Level1			-300
		Level2		0
			Level3	-10
			Level3	120
		Level2		-300
			Level3	-300

Level2 is 0 if the sum of Level3 is positive... from Level2 and upwards it should just be a classic SUM of Level2.

How can I change my data model  to do this custom aggregation?

[ovonel]

I need to do an agregattion across Project and Client table...

I have my Fact and a Project dimension:

Dimension Project:

Proj	Discount	Level1	Level2	Level3
1	.05	xyz	xyz	xyz
2	.10	xyz	xyz	xyz
3	.10	xyz	xyz	xyz
4	.5	xyz	xyz	xyz
5	.8	xyz	xyz	xyz
6	.1	xyz	xyz	xyz

Fact:

Proj	Client	Gross Fees	Net Revenue	Expected Revenue	Diff
1	1	243	20	230,85	-210,85
2	1	52	2000	46,8	1953,2
3	1	41	10	36,9	-26,9
4	2	341	35	170,5	-135,5
5	2	134	71	26,8	44,2
6	2	34	34	30,6	3,4

Expected Revenue is a calculated column: Revenue[GrossFees] - ( RELATED(Project[Discount]) * Revenue[GrossFees] )

Diff is also a calculated column: Net Revenue - Expected Revenue

I need to achieve a matrix like:

				Diff
Client				-300
	Level1			-300
		Level2		0
			Level3	-10
			Level3	120
		Level2		-300
			Level3	-300

Level2 is 0 if the sum of Level3 is positive... from Level2 and upwards it should just be a classic SUM of Level2.

How can I do this in DAX across the 2 dimensions? Do i need to change my data model  to do this custom matrix?

[amitchandak]

@ovonel , Is you measure is ?

sumx(Fact, [Net Revenue] - [Expected Revenue])

[jgeddes]

You should be able to use the ISINSCOPE function to help with this.
A quick example...
If you have data like

you can add a calculated column 

to get 

you can then write a measure using ISINSCOPE

and end up with 

Your actual solution will depend greatly on how your data model is organized, but hopefully this gets you pointed in the right direction.