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
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
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!
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.
Thanks for your help!
Ryan
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |