Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
datacauts
Helper I
Helper I

Calculate Alpha and Beta of a Linear Regression using DAX

Hello! I'm stuck in creating a measure that simulates a linear regression.
Basicaly I have two variables, benchmark (X) and stocks (Y). What I need to do is calculate the Alpha and Beta of a linear regression between them for the selected period. I tried to used the following structure:
 
alfa =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( mydata[date] ),
            "Known[X]", SUM(mydata[benchmark_x]),
            "Known[Y]", SUM(mydata[stock_allocation])
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )

VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X

RETURN
Intercept
 
where Alpha = Incercept and Beta = Slope
 
but the results doesn't match the correct expected values because DAX is just calculating the measure for every row/date in the series and summing all of them in the end. That's not what I want (editado)
 
Can someone help me with that? Will share the file here
 
the expected value for Alpha from 2002 to 2022 (no filters on Date) is 0,74. I achieve the expected results via Calculated columns, but they don't change with filters so It's not very useful for me
1 REPLY 1
Greg_Deckler
Super User
Super User

@datacauts Have you reviewed this? Simple Linear Regression - Microsoft Power BI Community

And this? De-Seasonalized Correlation Coefficient - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.