Drawing trend line with categorical X axis [DAX]

[Anonymous]

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/DAX-to-create-a-Trend-line/m-p/398438/highlight/true#M18188...

- 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:

[amitchandak]

@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

[Anonymous · Accepted 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]
    )

yay

Edit: Corrected measure, now works ok.