Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have a simple table:
Rank | Price |
1 | 10 |
2 | 15 |
3 | 3 |
4 | 57 |
5 | 23 |
6 | 41 |
7 | 12 |
8 | 35 |
9 | 21 |
I´d like to write a measure that returns a trend line.
I´ve read several posts but none helped.
- https://community.powerbi.com/t5/Desktop/Trend-line-x-axis/m-p/218665#M96973
- https://xxlbi.com/blog/simple-linear-regression-in-dax/
This measure:
Simple linear regression =
VAR Known =
FILTER (
SELECTCOLUMNS (
CALCULATETABLE( VALUES( 'Table'[Rank] ); ALLSELECTED ( 'Table'[Rank] ) );
"Known[X]"; 'Table'[Rank];
"Known[Y]"; SUM( 'Table'[Price] )
);
AND (
NOT ( ISBLANK ( Known[X] ) );
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known; Known[X] )
VAR Sum_X2 =
SUMX ( Known; Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known; Known[Y] )
VAR Sum_XY =
SUMX ( Known; Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known; Known[X] )
VAR Average_Y =
AVERAGEX ( Known; Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y;
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
SUMX (
DISTINCT ( 'Table'[Rank] );
Intercept + Slope * 'Table'[Rank]
)
Returns the same line:
Solved! Go to Solution.
Hi @amitchandak, thanks for the tip, it helped me get the answer.
ALL(Known) does not work because you can´t use ALL with a table expression.
VAR Sum_X =
SUMX ( ALL ( Known ); Known[X] )
So I created two calculated columns:
Rank_2 = 'Table'[Rank] * 'Table'[Rank]
Rank_Price = 'Table'[Rank] * 'Table'[Price]
And used the measure with the table reference:
Simple linear regression =
VAR Count_Items =
CALCULATE( COUNTROWS ( 'Table' ); ALL( 'Table' ) )
VAR Sum_X =
SUMX ( ALL( 'Table' ); 'Table'[Rank] )
VAR Sum_X2 =
SUMX ( ALL( 'Table' ); 'Table'[Rank_2] )
VAR Sum_Y =
SUMX ( ALL( 'Table' ); 'Table'[Price] )
VAR Sum_XY =
SUMX ( ALL( 'Table' ); 'Table'[Rank_Price] )
VAR Average_X =
AVERAGEX ( ALL( 'Table' ); 'Table'[Rank] )
VAR Average_Y =
AVERAGEX ( ALL( 'Table' ); 'Table'[Price] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y;
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
SUMX (
DISTINCT ( 'Table'[Rank] );
Intercept + Slope * 'Table'[Rank]
)
Edit: Corrected measure, now works ok.
@Anonymous . Check your formula, put values in table visual, and check. I think it taking row context.
Where possible put all(known) at place known and check
Hi @amitchandak, thanks for the tip, it helped me get the answer.
ALL(Known) does not work because you can´t use ALL with a table expression.
VAR Sum_X =
SUMX ( ALL ( Known ); Known[X] )
So I created two calculated columns:
Rank_2 = 'Table'[Rank] * 'Table'[Rank]
Rank_Price = 'Table'[Rank] * 'Table'[Price]
And used the measure with the table reference:
Simple linear regression =
VAR Count_Items =
CALCULATE( COUNTROWS ( 'Table' ); ALL( 'Table' ) )
VAR Sum_X =
SUMX ( ALL( 'Table' ); 'Table'[Rank] )
VAR Sum_X2 =
SUMX ( ALL( 'Table' ); 'Table'[Rank_2] )
VAR Sum_Y =
SUMX ( ALL( 'Table' ); 'Table'[Price] )
VAR Sum_XY =
SUMX ( ALL( 'Table' ); 'Table'[Rank_Price] )
VAR Average_X =
AVERAGEX ( ALL( 'Table' ); 'Table'[Rank] )
VAR Average_Y =
AVERAGEX ( ALL( 'Table' ); 'Table'[Price] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y;
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
SUMX (
DISTINCT ( 'Table'[Rank] );
Intercept + Slope * 'Table'[Rank]
)
Edit: Corrected measure, now works ok.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |