Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Post Patron
5 years ago

Calculating SLOPE with DAX Formula

Hello Community - 

 

I have a table that contains Part #, Release Dates, Quantity and PO #.  There can be multiple lines for each date and each part for each date (ex. Part 123 could have 5 lines on it for release date 1/1/2021).  I am looking to calculate the SLOPE of the trend over a period of time.  Shocking to me that Power BI does not have a DAX function for it yet.  

 

Here is my current calculation I am using for SLOPE.  It is appearing to work for some part numbers, but not for others (slope should be negative and it is showing positive for some).

 

Slope = 

VAR _AvgX = CALCULATE(
    AVERAGEX('Kyle TeslaReleases','Kyle TeslaReleases'[ReleaseDate_Mon]),ALLSELECTED('Kyle TeslaReleases'[ReleaseDate_Mon]))

VAR _AvgY = CALCULATE(
    AVERAGEX('Kyle TeslaReleases','Kyle TeslaReleases'[Quantity]),ALLSELECTED('Kyle TeslaReleases') )

RETURN

DIVIDE(
    SUMX('Kyle TeslaReleases',('Kyle TeslaReleases'[ReleaseDate_Mon]- _AvgX) * 'Kyle TeslaReleases'[Quantity]-_AvgY),
    SUMX('Kyle TeslaReleases',('Kyle TeslaReleases'[ReleaseDate_Mon] - _AvgX) * ('Kyle TeslaReleases'[ReleaseDate_Mon]-_AvgX ) ) )

 

I cannot figure out the reason for the inconsistencies.  I am not sure if it is due to parts have multiple lines for each release date, or if I am just missing something basic in my current measure.

 

Any help would be greatly appreciated.

Thank You

Ryan

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    hi ryan_b_fiting - would you be able to share the pbix file or a sample file with some mocked up data so I can look at what is happening with the DAX. 

    Happy to help! 

    • ryan_b_fiting's avatar
      ryan_b_fiting
      Post Patron

      Anonymous thanks for the response.  

      I have attached some sample data (with sensitive data removed/adjusted).

      Let me know if this works for you.

       

      Sample File 

       

      Thanks for your help!

      Ryan