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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ryan_b_fiting
Post Patron
Post Patron

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 2
Sumanth_23
Memorable Member
Memorable Member

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! 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



@Sumanth_23 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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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