Simple Linear Regression

This measure allows you to predict dependent values Y from independent values X. NAME: Simple linear regression DESCRIPTION: Estimate Y values based on X values. PARAMETERS: N...

Simple linear regression.pbix66 KB

Mathematical

Daniil

Kudo Kingpin

8 years ago

Tony, you should visit the local Power BI User Group if you get a chance :-) The next meeting date should be announced at Meetup soon.

Try the following measures:

Starting Temperature = 
VAR Estimate =
    SELECTCOLUMNS (
        KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
        "Estimate[X]", 'cetdata'[YEAR],
        "Estimate[Y]", [Estimated]
    )
VAR First =
    TOPN ( 1, Estimate, Estimate[X], ASC )
RETURN
    MINX ( First, Estimate[Y] )

Ending Temperature = 
VAR Estimate =
    SELECTCOLUMNS (
        KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
        "Estimate[X]", 'cetdata'[YEAR],
        "Estimate[Y]", [Estimated]
    )
VAR Last =
    TOPN ( 1, Estimate, Estimate[X], DESC )
RETURN
    MAXX ( Last, Estimate[Y] )

Temperature Difference = [Ending Temperature] - [Starting Temperature]

tonymaclaren

Helper I

8 years ago

Dear Daniil

Thank you very much--all working perfectly now. KEEPFILTERS did the trick.I have really learned a lot! I have now ordered the SQLBI book.

One strange thing however,The [Estimated] measure which is your Simple linear Regression measure produces a total if displayed in a table. I do not quite understand this.

Best Regards

and thanks again

Tony

Daniil
Kudo Kingpin
8 years ago
tonymaclaren, it is likely that at the grand total level what you see is not a total, but the Intercept. This is because the following expression evaluates to Intercept:

Intercept + Slope * SELECTEDVALUE ( 'cetdata'[YEAR] )

At the grant total level, there is usually more than one 'cetdata'[YEAR], hence SELECTEDVALUE ( 'cetdata'[YEAR] ) returns BLANK, turning the multiplication into BLANK as well, leaving only Intercept.

I updated my blog post to deal with this kind of situations.

Anonymous, glad you found this useful!
- tonymaclaren
  Helper I
  8 years ago
  Dar Daniil
  
  Thanks for the explanation.The same value also appears at the top of the [Estimated] measure, but only if the whole of the data for ('Ctedata'[YEAR])is displayed. is there a modification to eliminate this?
  
  Tony
  - Daniil
    Kudo Kingpin
    8 years ago
    Tony, it's a bit difficult to advise anything without seeing your report -- can you share a sample of your data?