Forum Discussion
How to change data model to do a custom matrix?
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.
3 Replies
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
Super User
Anonymous , Is you measure is ?
sumx(Fact, [Net Revenue] - [Expected Revenue])
jgeddesYou should be able to use the ISINSCOPE function to help with this.
A quick example...
If you have data likeyou 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.
