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]
)
OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

matthewtay  you're welcome!

On your questions:

  1. I would recommend adjusting the Trend Line measure to hide values that are outside the date range present in the data table. One way of doing this is shown below, by comparing the global min/max dates in the data table with the filtered min/max dates.
  2. The trend calculation line itself should already adjust based on the overall filter context of the visual due to the use of ALLSELECTED. I just tweaked my measures to use ALLSELECTED without arguments, as this should ensure that all filters at the "overall" visual level are retained. When changing the date range, the trend line appears to adjust as you would expect. Does this work for you?

Updated PBIX attached.

Trend line = 
VAR DataMinDate =
    CALCULATE ( MIN ( data[SubmissionDate] ), REMOVEFILTERS () )
VAR DataMaxDate =
    CALCULATE ( MAX ( data[SubmissionDate] ), REMOVEFILTERS () )
VAR FilteredMinDate = MIN ( 'Date'[Date] )
VAR FilteredMaxDate = MAX ( 'Date'[Date] )
VAR Result =
    IF (
        AND ( FilteredMinDate <= DataMaxDate, FilteredMaxDate >= DataMinDate ),
        VAR Known =
            FILTER (
                SELECTCOLUMNS (
                    CALCULATETABLE (
                        SUMMARIZE ( 'data', 'Date'[Date] ),
                        ALLSELECTED ( )
                    ),
                    "Known[X]", 'Date'[Date],
                    "Known[Y]", [Percentage of Abnormal Results]
                ),
                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
            AVERAGEX (
                DISTINCT ( 'Date'[Date] ),
                Intercept + Slope * 'Date'[Date]
            )
    )
RETURN
    Result

Regards

Supi's avatar
Supi
New Member
1 year ago

Many thanks for such a brilliant solution. It works perfectly with my use case.