Using several variables in a complex measure returning incorrect result

[WBscooby]

Hi

I'd be grateful for some help. I've created a complex measure to add a regression line to a  line and clustered column chart. If I create all of the variables as separate measures, the line works perfectly.  However, it would be preferable to create as variables. When I use the measure below, I get different, incorrect results. Can anyone advise? Thank you 🙂 

Linear HW =
VAR Count_Items =
CALCULATE( COUNTROWS ( 'SALES' ), ALL( 'SALES') ,'SALES'[Town]="HW")

Var SumRank = SUM('SALES'[Dense Rank])

VAR Sum_X =
CALCULATE(SumRank,
FILTER(
ALL('SALES' ),
'SALES' [Town]="HW"
)
)

Var RankSqd = sumx('SALES','SALES'[Dense Rank]^2)

VAR Sum_X2 =
CALCULATE((RankSqd),
FILTER(
ALL('SALES' ),
'SALES' [Town]="HW"
)
)

Var SumValues = sum( 'SALES'[Rate per 100,000])

VAR Sum_Y =
CALCULATE(SumValues,
FILTER(
ALL('SALES' ),
'SALES' [Town]="HW"
)
)

Var YearSort_Value = Sumx('SALES','SALES'[Dense Rank]* 'SALES'[Rate per 100,000])

VAR Sum_XY =
CALCULATE(YearSort_Value,
FILTER(
ALL('SALES' ),
'SALES' [Town]="HW"
)
)

VAR Average_X =
Calculate(AVERAGEX('SALES','SALES'[Dense Rank]),
FILTER(
ALL('SALES'),
'SALES'[Town]="HW"
)
)

VAR Average_Y =
Calculate(AVERAGEX('SALES','SALES'[Rate per 100,000]),
FILTER(
ALL('SALES'),
'SALES'[Town]="HW"
)
)

VAR Slope =
calculate(
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2),
FILTER(
ALL('SALES'),
'SALES'[Town]="HW"
)
)

VAR Intercept =
CALCULATE( Average_Y - Slope * Average_X,
FILTER(
ALL('SALES'),
'SALES'[Town]="HW"
)
)

RETURN

Intercept + MAX('SALES'[Dense Rank])*Slope

[WBscooby · Accepted solution]

I've managed to solve this with a little help from Chat OpenAI to understand the code. I needed to use SUMX for some of the variables within the calculation as below:

// X = Dense Rank, Y = Rate per 100,000

VAR Count_Items_Calc = CALCULATE(COUNT('SALES'[Town]), ALL('SALES'), 'SALES'[Town]= "HW")
VAR Sum_X_Calc = CALCULATE(SUM('SALES'[Dense Rank]), ALL('SALES'), 'SALES'[Town] = "HW")
VAR Sum_Y_Calc = CALCULATE(SUM('SALES'[Rate per 100,000]), ALL('SALES'), 'SALES'[Town] = "HW")
VAR Sum_XY_Calc = CALCULATE(sumx('SALES','SALES'[Dense Rank]*'SALES'[Rate per 100,000]), ALL('SALES'), 'SALES'[Town] = "HW")
VAR Sum_X2_Calc = CALCULATE(SUMX('SALES','SALES'[Dense Rank] ^ 2), ALL('SALES'), 'SALES'[Town] = "HW")
VAR Average_X = CALCULATE(AVERAGEX('SALES','SALES'[Dense Rank]), ALL('SALES'), 'SALES'[Town] = "HW")
VAR Average_Y = CALCULATE(AVERAGEX('SALES','SALES'[Rate per 100,000]), ALL('SALES'), 'SALES'[Town] = "HW")
VAR Slope =
CALCULATE(
DIVIDE(
Count_Items_Calc * Sum_XY_Calc - Sum_X_Calc * Sum_Y_Calc,
Count_Items_Calc * Sum_X2_Calc - Sum_X_Calc ^ 2
)
)

VAR Intercept =

CALCULATE( Average_Y - Slope * Average_X,
FILTER(
ALL('SALES'),
'SALES'[Town]="HW"
)
)

Return

Intercept + MAX('SALES'[Dense Rank])*slope