@dsiffredi,

OK, here goes… This one ended up being really interesting - here’s what I did:

As anticipated, setting the supporting revenue scaling factor table up correctly makes it much simpler. I took your Revenue Conversion Rates Table and unpivoted all the month columns (and added a fiscal year column to make the solution more “durable” over time). Supporting table now looks like this:

Now the measure to look up the proper scaling factor becomes a pretty straightforward CALCULATE/FILTER construct, filtering on fiscal year, period and short month:

```
Lookup Scaling Factor =
VAR SelMo = SELECTEDVALUE( Dates[ShortMonth] )
VAR SelPer = SELECTEDVALUE( Opportunities[Period] )
VAR SelFY = SELECTEDVALUE( Dates[FYNum] )
VAR ScalFctr =
CALCULATE(
SELECTEDVALUE( 'Revenue Conversion Rates'[Scaling Factor] ),
FILTER(
'Revenue Conversion Rates',
'Revenue Conversion Rates'[Scaling Factor FY] = SelFY &&
'Revenue Conversion Rates'[Period] = SelPer &&
'Revenue Conversion Rates'[Short Month] = SelMo
)
)
RETURN
ScalFctr
```

Then scaling the revenue becomes a very simple matter of multiplying revenue by the appropriate scaling factor(s):

```
Spread Revenue =
[Total Opportunity Revenue] * [Lookup Scaling Factor]
```

So far, so good. Just drop this measure into the matrix and…[cue sad trombone sound]

The individual cells calculate correctly, but the totals do not since they don’t have sufficient evaluation context to calculate the correct scaling factor(s). So now we have to create a measure with a virtual table of all combinations of period and short month (using CROSSJOIN) and calculate the scaling factor and scaled revenue for each row of that table. Then there are four conditions to evaluate:

- both period and short month have one value - skip the virtual table and just use the measure above
- short month has one value, but period does not (column totals) - SUMX of the virtual scaled revenue column over all values of period
- period has one value, but short month does not (row totals) - SUMX of the virtual scaled revenue column over all values of short month
- neither field has one value (grand total) - straight SUMX of virtual scaled revenue column

Here’s the measure implementing this logic:

```
Spread Revenue with Totals =
VAR SelMo = SELECTEDVALUE( Dates[ShortMonth] )
VAR SelPer = SELECTEDVALUE( Opportunities[Period] )
VAR SelFY = SELECTEDVALUE( Dates[FYNum] )
VAR ScalFctr =
CALCULATE(
SELECTEDVALUE( 'Revenue Conversion Rates'[Scaling Factor] ),
FILTER(
'Revenue Conversion Rates',
'Revenue Conversion Rates'[Scaling Factor FY] = SelFY &&
'Revenue Conversion Rates'[Period] = SelPer &&
'Revenue Conversion Rates'[Short Month] = SelMo
)
)
VAR vTable =
ADDCOLUMNS(
CROSSJOIN(
VALUES( Opportunities[Period] ),
VALUES( Dates[ShortMonth] )
),
"Factor", [Lookup Scaling Factor],
"SprRev", [Spread Revenue]
)
VAR TotSpredRev =
IF( HASONEVALUE( Opportunities[Period] ) && HASONEVALUE( Dates[ShortMonth] ),
[Spread Revenue],
IF( HASONEVALUE( Dates[ShortMonth] ),
CALCULATE(
SUMX(
vTable,
[SprRev]
),
VALUES( Opportunities[Period] )
),
IF( HASONEVALUE( Opportunities[Period] ),
CALCULATE(
SUMX(
vTable,
[SprRev]
),
VALUES( Dates[ShortMonth] )
),
SUMX(
vTable,
[SprRev]
)
)
)
)
RETURN
TotSpredRev
```

Now drop this in the matrix values well, and boom… now all totals work just as expected

Now your visuals should be easy to generate from this matrix and the above measures.

Really fun problem. Enjoyed working with you on this – hope it’s helpful. Full solution file posted below.