Forum Discussion

MarkSL's avatar
MarkSL
Icon for Helper V rankHelper V
8 years ago
Solved

DAX to create a Trend line?

Hi,   I really want to add a Trend Line to a Line and stacked column chart, however the Analytics options aren't availabe for this type of visual.  Is it possible to create a Trend using DAX , the ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi MarkSL

     

    Just tested it out and the issue is with ALLSELECTED ( 'DateTable'[Date] ). It doesn't work as intended when you filter on a column other that Date, such as Month.

     

    One possible fix is the change in red below.

    I have restated your entire code for completeness.

     

    That should work (tested a mock-up model at my end) but let me know if it doesn't

     

    Oh, by the way, there is a "Combine Series" setting for trendlines that determines whether each series gets its own trend line.

     

    Regards,

    Owen

     

    Estimated Sales =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            CALCULATETABLE ( VALUES ( 'DateTable'[Date] ), ALLSELECTED ('DateTable') ),
            "Known[X]", 'DateTable'[Date],
            "Known[Y]", [SalesDaily2]
        ),
        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 ( 'DateTable'[Date] ),
    Intercept + Slope * 'DateTable'[Date]
)
MarkSL's avatar
MarkSL
Icon for Helper V rankHelper V
8 years ago

Ah, I may have spoken to soon of success...

 

It appears that when I filter on month, to reduce the dataset, my calculated trend does not match the automatic trend.  However, if I filter on another field, such as customer, the two lines do still match??  I am very new to DAX and so am not sure why this is happening?

 

Here is the code for the calculated trend:

 

Estimated Sales = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'DateTable'[Date] ),
            "Known[X]", 'DateTable'[Date],
            "Known[Y]", [SalesDaily2]
        ),
        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 ( 'DateTable'[Date] ),
        Intercept + Slope * 'DateTable'[Date]
    )

 

Thanks again.

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
8 years ago

Hi MarkSL

 

Just tested it out and the issue is with ALLSELECTED ( 'DateTable'[Date] ). It doesn't work as intended when you filter on a column other that Date, such as Month.

 

One possible fix is the change in red below.

I have restated your entire code for completeness.

 

That should work (tested a mock-up model at my end) but let me know if it doesn't

 

Oh, by the way, there is a "Combine Series" setting for trendlines that determines whether each series gets its own trend line.

 

Regards,

Owen

 

Estimated Sales =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            CALCULATETABLE ( VALUES ( 'DateTable'[Date] ), ALLSELECTED ('DateTable') ),
            "Known[X]", 'DateTable'[Date],
            "Known[Y]", [SalesDaily2]
        ),
        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 ( 'DateTable'[Date] ),
    Intercept + Slope * 'DateTable'[Date]
)