Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
@ovonel , Is you measure is ?
sumx(Fact, [Net Revenue] - [Expected Revenue])
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.
Proud to be a Super User! | |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |