DAX / PP Aggregate a variable project margin down a column

Question

I need a solution similar to this:

DAX running total (or count) across 2 groups

However slightly more complex.

I have the following: (apologies for the layout - i can't post pictures)

Name      Date       Monthly Rev  Total Rev   Margin( % Rev)
Proj 1    1/08/2014   0              7000     15%
Proj 1    1/09/2014   1000           7000     15%
Proj 1    1/10/2014   1000           7000     15%
Proj 1    1/11/2014   1000           7000     15%
Proj 1    1/12/2014   0              7000     15%
Proj 1    1/01/2015   0              7000     15%
Proj 1    1/02/2015   2000           7000     15%
Proj 1    1/03/2015   2000           7000     15%
Proj 2    1/11/2014   0              16000    10%
Proj 2    1/12/2014   1500          16000     10%
Proj 2    2/12/2014   1500           16000    10%
Proj 2    3/12/2014   1500          16000     10%
Proj 2    4/12/2014   1500           16000    10%
Proj 2    5/12/2014   2000           16000    10%
Proj 2    6/12/2014   2000            16000   10%
Proj 2    7/12/2014   0               16000   10%
Proj 2    8/12/2014   2000            16000   10%
Proj 2    9/12/2014   2000            16000   10%
Proj 2    10/12/2014  2000           16000    10%

Monthly rev is the revenue received in a month, total is the total project value and margin is the percentage of revenue. The table is linked to a dates table by Date.

I need to show margin by date (there are other descriptive columns in the table for slicing) however the margin calc is not straightforward.

In an excel table it would look something like this:

Cumm simple margin  | Completion|   Cumm complex margin |   Margin earnt
0                        0%                 0                   0
150                      20%               30                   30
300                      40%               120                  90
450                     60%                 270                150
450                     60%                270                  0
450                      60%              270                   0
750                      80%              600                  330
1050                    100%               1050               450
0                        0%                  0                  0
150                      11%               17                   17
300                      22%               67                  50
450                     33%                150                   83
600                      44%               267                  117
800                      56%                444                 178
1000                    67%                 667                 222
1000                    67%                667                   0
1200                   78%                 933                  267
1400                    89%               1244                  311
1600                    100%              1600                  356

Where:

Simple margin is calculated on a cumulative basis as % of monthly Rev
Percentage complete of the project is calculated based on "active" months where revenue is earned
Cumulative simple margin is multiplied by the % complete
Actual margin earned in a particular month is the difference between two months.

Note that Monthly revenue is not necessarily continuous.

No idea how to recreate this in power pivot, any suggestions would be well received.

Cheers

Gordon K Gordon K · Accepted Answer · 2015-07-09T13:17:37

Assuming

That your Project 2 data should run monthly from 1/11/2015 to 1/09/2015 (rather than individual December dates)
You have your data in a table called 'ProjectMargins'
Your DateDim table is called 'Reporting Dates'

Then these are the DAX Measures you need (although there may be simpler methods for achieving these results):

[MonthlyRev]:=SUM(ProjectMargins[Monthly Rev])

[ActiveMonth]:=CALCULATE(COUNTROWS('ProjectMargins'),FILTER('ProjectMargins',[MonthlyRev]>0))

[AllActiveMonths]:=CALCULATE([ActiveMonth],ALL('Reporting Dates'[Date]))

[Completion]:=DIVIDE(CALCULATE([ActiveMonth],FILTER(ALL('Reporting Dates'[Date]),'Reporting Dates'[Date] <= MAX(ProjectMargins[Date]))),[AllActiveMonths])

If you need to calculate TotalRev, from your Monthly Rev, Rather than it appearing in the original source table: [TotalRev]:=IF(ISBLANK(MAX(ProjectMargins[Margin( % Rev)])),BLANK(),CALCULATE([MonthlyRev],ALL('Reporting Dates'[Date])))

[Rev%]:=MAX(ProjectMargins[Margin( % Rev)])

[Cumm Simple Margin]:=CALCULATE([MonthlyRev]*[Rev%],FILTER(ALL('Reporting Dates'[Date]),'Reporting Dates'[Date] <= MAX(ProjectMargins[Date])))

[Cumm Complex Margin]:=[Completion]*[Cumm Simple Margin]

[Previous Month Cumm Complex]:=CALCULATE([Cumm Complex Margin], DATEADD('Reporting Dates'[Date],-1,MONTH))

[Margin Earnt]:=IF([Cumm Complex Margin]>0,[Cumm Complex Margin]-[Previous Month Cumm Complex],BLANK())

NOTE: This assumes that the margin is never negative.

Ensure that the date field from the DateDim table is used in your pivot, not the date field from the Fact table.

enter image description here

DAX / PP Aggregate a variable project margin down a column

1 Answers