Forum Discussion
replicate Excel SLOPE calculation
Hi Community
I need to re-create this Excel formula calculation (Slope) in Power BI.
So basically the known x is number from 1 to 24 represent 24 months of data, from month 12, the slope calculation is known y = the unit value in row 3 for the past 12 columns and the known x = the sequence number in row 1 for the past 12 columns
as shown in the below Excel formula bar, any idea how to calculation this figure using DAX? Thanks
Hi, Please check the below picture and the attached pbix file.
Volume total: = SUM( Data[Volume] )WINDOW function (DAX) - DAX | Microsoft Learn
Slope: = VAR _calendar = ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month sort] ) VAR _window = WINDOW ( -11, REL, 0, REL, _calendar, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ) VAR _condition = COUNTROWS ( FILTER ( _window, [Volume total:] <> BLANK () ) ) = 12 VAR _t = ADDCOLUMNS ( _window, "@x", RANK ( SKIP, _calendar, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ), "@y", [Volume total:] ) VAR _linestx = LINESTX ( _t, [@y], [@x] ) RETURN IF ( _condition, MAXX ( _linestx, [Slope1] ) )
9 Replies
Hi,
I'd also need exactly how to recreate this slope formula in power BI, could you please write the complete DAX formula for this request?:
I need to re-create this Excel formula calculation (Slope) in Power BI.
So basically the known x is number from 1 to 24 represent 24 months of data, from month 12, the slope calculation is known y = the unit value in row 3 for the past 12 columns and the known x = the sequence number in row 1 for the past 12 columns
as shown in the below Excel formula bar, any idea how to calculation this figure using DAX? Thanks
Hi,
The image is too small for me. Even if I click the image to make it larger, it is still too small.
Could you please provide your sample pbix file's link & how the expected result looks like? And then, I can try to look into it.
Thank you.
thank you Kim. I realise i can't upload a file or store somewhere online with link...i have pasted the actual figures as below, if this is not handy, you can use dummy numbers as well, doesnt matter.
the key desired the outcome is to create a measure in Power BI called slope and the calculation of Slope in Sep 22 should be 35.8, as the Excel forumla spit out. and Oct 22 slope should be 20.2 ....
i have repasted the bigger photo with what's the Excel forumla looks like
if you use dummy data, as long as it match same data using the Excel "Slope" formula" calculation, it would be all perfect to adapt. Thank you
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Month Oct.21 Nov.21 Dec.21 Jan.22 Feb.22 Mar.22 Apr.22 May.22 Jun.22 Jul.22 Aug.22 Sep.22 Oct.22 Nov.22 Dec.22 Jan.23 Feb.23 Mar.23 Apr.23 May.23 Jun.23 Jul.23 Aug.23 Sep.23 Volume 2,828 3,247 3,313 2,775 2,912 3,299 2,925 3,149 3,193 3,172 3,524 3,402 3,114 3,373 3,560 3,032 3,164 3,384 3,227 3,719 3,323 3,578 3,669 3,490 Hi, Please check the below picture and the attached pbix file.
Volume total: = SUM( Data[Volume] )WINDOW function (DAX) - DAX | Microsoft Learn
Slope: = VAR _calendar = ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month sort] ) VAR _window = WINDOW ( -11, REL, 0, REL, _calendar, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ) VAR _condition = COUNTROWS ( FILTER ( _window, [Volume total:] <> BLANK () ) ) = 12 VAR _t = ADDCOLUMNS ( _window, "@x", RANK ( SKIP, _calendar, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ), "@y", [Volume total:] ) VAR _linestx = LINESTX ( _t, [@y], [@x] ) RETURN IF ( _condition, MAXX ( _linestx, [Slope1] ) )
Thanks a lot Jihwan Kim, your solution for the slope works perfectly!
