cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovonel
Post Prodigy
Post Prodigy

How to change data model to do a custom matrix?

I have my Fact and a Project dimension:

Dimension Project:

ProjDiscountLevel1Level2Level3
1.05xyzxyzxyz
2.10xyzxyzxyz
3.10xyzxyzxyz
4.5xyzxyzxyz
5.8xyzxyzxyz
6.1xyzxyzxyz

 

 

Fact:

ProjClientGross FeesNet RevenueExpected RevenueDiff
1124320230,85-210,85
2152200046,81953,2
31411036,9-26,9
4234135170,5-135,5
521347126,844,2
62343430,63,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
   Level3120
     
  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?

 

3 REPLIES 3
ovonel
Post Prodigy
Post Prodigy

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

 

I have my Fact and a Project dimension:

Dimension Project:

ProjDiscountLevel1Level2Level3
1.05xyzxyzxyz
2.10xyzxyzxyz
3.10xyzxyzxyz
4.5xyzxyzxyz
5.8xyzxyzxyz
6.1xyzxyzxyz

 

 

Fact:

ProjClientGross FeesNet RevenueExpected RevenueDiff
1124320230,85-210,85
2152200046,81953,2
31411036,9-26,9
4234135170,5-135,5
521347126,844,2
62343430,63,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
   Level3120
     
  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?

 

@ovonel , Is you measure is ?

 

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

jgeddes
Solution Sage
Solution Sage

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

jgeddes_0-1669669925495.png

you can add a calculated column 

jgeddes_1-1669669961481.png

to get 

jgeddes_2-1669669974754.png

you can then write a measure using ISINSCOPE

jgeddes_3-1669670041451.png

and end up with 

jgeddes_4-1669670065457.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.