As an idea for an alternative, here is a window function based solution. Still needs a CTE as window function cannot be directly used as a parameter in another window function.
😎
USE tempdb;
GO
;WITH REP_BASE AS
(
SELECT
AT.SalesID
,AT.AccountID
,AT.FiscalMonth
,DENSE_RANK() OVER
(
PARTITION BY AT.SalesID
,AT.AccountID
,AT.FiscalMonth
ORDER BY AT.SalesID
,AT.AccountID
,AT.FiscalYear
) AS NUM_YEAR_FCM
,SUM(AT.Revenue)OVER
(
PARTITION BY AT.SalesID
,AT.AccountID
,AT.FiscalMonth
) AS ACNT_FM_SUM
,ROW_NUMBER() OVER
(
PARTITION BY AT.SalesID
,AT.AccountID
,AT.FiscalMonth
ORDER BY AT.SalesID ASC
,AT.AccountID ASC
,AT.FiscalYear DESC
) AS ACNT_FM_RID
FROM dbo.AggTest AT
)
SELECT
RB.SalesID
,RB.AccountID
,RB.FiscalMonth
,RB.NUM_YEAR_FCM AS [#ofYears]
,RB.ACNT_FM_SUM AS Revenue
,(RB.ACNT_FM_SUM / RB.NUM_YEAR_FCM) AS AvgByAccount
,SUM(RB.ACNT_FM_SUM / RB.NUM_YEAR_FCM) OVER
(
PARTITION BY RB.FiscalMonth
) AS totavg
,(RB.ACNT_FM_SUM / RB.NUM_YEAR_FCM * 1.0)
/ SUM((RB.ACNT_FM_SUM / RB.NUM_YEAR_FCM * 1.0)) OVER
(
PARTITION BY RB.FiscalMonth
) AS ExpansionRatio
FROM REP_BASE RB
WHERE RB.ACNT_FM_RID = 1;
Results
SalesID AccountID FiscalMonth #ofYears Revenue AvgByAccount totavg ExpansionRatio
------- --------- ----------- --------- -------- ------------- ------- --------------------
1 1 1 3 60 20 115 0.1739130434782608
1 2 1 2 150 75 115 0.6521739130434782
1 3 1 2 40 20 115 0.1739130434782608
1 1 2 1 5 5 305 0.0163934426229508
1 2 2 1 50 50 305 0.1639344262295081
1 3 2 2 500 250 305 0.8196721311475409