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

    Thanks both for the quick replies.  

     

    Owen, I tried the second Dax example from your first link (Other ways to use the pattern) and I seem to have created my trend line as desired!  However, it has raised another question about the provided Trend Line provided in Analytics...

     

    I first added my new calculated trend line (yellow) to the my Line chart to check that it matched the previously applied automatic trend line (dashed), but found that it sat above it and not on it:

     

     

    So I initially thought something was not quite right.  However, when I removed the Budget line (red) from the chart, the automatic Trend line moved to sit exactly on my created trend line:

     

     

    On looking at the automatic Trend line provided by Analytics, it doesn't ask which measure (line) on the chart you want the trend to be based on, which seems a bit odd to me?  I assume that it must be based on both measures, Sales and Budget?

     

    Anyway, I don't require the Line chart as my trend line is now showing as desired on my Line and Bar chart which is what I really wanted:

     

     

    I would be interested to hear your thoughts on the automatic Trend line moving though?

     

    Thanks

     

    Mark

    • 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]
)