Forum Discussion

Helper 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

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

Helper 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

Helper 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
Super 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] )
- Anonymous
  Not applicable
  7 years ago
  Hi Owen,
  
  Thanks for the fantastic code!
  I used your code to create a trend line, it's working great, but only calculates a trend for the entire dataset. I have a list of facilities, and I want the trend line to dynamically update when I select different facilities using a page level filter. I'm trying to change this so it can adapt to visual filters, but not much luck, and hope you might be able to help. Thanks a lot in advane!
  Cheers,
  Michael
- ECE
  Advocate II
  4 years ago
  Hi Owen,
  
  I know this is an old thread, but I found this solution very interesting.
  However, I am haveing issues changing above code, to account for the X-axis being in text format, for example YearMonth (YYYY-MM).
  Any chance you have an idea how to accomplish this?
  
  Any help would be highly appreciated,
  Espen
  - OwenAuger
    Super User
    3 years ago
    ECE
    
    I'm sorry, this is a very late reply to your reply 😓
    
    If you are dealing with YearMonth as a text column, I would sugget adding a YearMonth index to your Date table and using that instead.
    
    To illustrate in DAX, the formula would be something like:
    
    YearMonth Index = YEAR ( 'Date'[Date] ) * 12 + MONTH ( 'Date'[Date] ) - 1
    
    The reason for subtracting one is that the Year or Month can be recovered from the Index using integer division or the modulo operator.
    
    This index increases by one for each month, so can be used in the regression calculation.
    
    Regards,
    
    Owen
- MarkSL
  Helper V
  8 years ago
  Hi OwenAuger
  
  Brilliant, thank you very much for looking at this and finding the fix. My calculated trend line now matches that of the automatic trend line when using all filters; Customer & Month.
  
  Thanks also for alerting me to the Combine Series option.
  
  Regards
  
  Mark
  - ThomasDay
    Impactful Individual
    7 years ago
    Hello OwenAuger, I wonder if I could get a little help here on what surely seems to be an easy situation but confounding me nonetheless.
    
    I'm looking to create a column in one table with the slope of trended metrics from another.
    
    One table (on the right) is current values of providers and associated metrics which I use for all sorts of things. I'd like to add a column with the slope of the trend for each provider/metric combination. There are ~600 metrics for each of ~4000 hospitals to compute a slope for.
    The other table (on the left) is a table of trend values...of these provider/metric combinations. So it contains the same ~600 metrics over as many as 8 years for the same ~4000 hospitals.
    Each table has a Variable_Name and Provdrno. I want to use the Provdrno AND Variable name from the row in the Right Table to give me the selection of trend values in the LEFT Table. Seems eacy enough
    So I've read what seem to be the relevant posts....and tried a variety of attacks. Here's the one that does seem promising.
    TempSlope = VAR RowMetric = SelectedValue(HospMeasures[Variable_Name]) VAR RowProviderID = SelectedValue(HospMeasures[PROVDRNO]) VAR Known = SELECTCOLUMNS ( CALCULATETABLE ( ALL(TR_MeasureTrends), TR_MeasureTrends[PROVDRNO] = RowProviderID , Tr_MeasureTrends[Variable_Name] = RowMetric ), "Known[X]", Tr_MeasureTrends[FY_Trend] , "Known[Y]", Tr_MeasureTrends[Variable_Value] ) VAR Count_Items = COUNTROWS ( Known ) VAR Sum_X = SUMX ( Known, VALUE(Known[X]) ) VAR Sum_X2 = SUMX ( Known, VALUE(Known[X]) ^ 2 ) VAR Sum_Y = SUMX ( Known, Known[Y] ) VAR Sum_XY = SUMX ( Known, VALUE(Known[X]) * Known[Y] ) VAR Average_X = AVERAGEX ( Known, VALUE(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 ) RETURN Slope
    Unfortunately every row has the same value! ugggg.....It's very mysterious.
    I believe the codes does the following
    creates VAR's for the relevant row info (ProvdrNo and Variable_Name)
    Select columns from the trend table where I
    clear all filters at the row level,
    filter for the proper provdrno and variable_name
    select the two columns I need for the slope calculation
    convert the FY_date column to a number as required.
    Do the calcs.
    Yet every value is identical. As I thrashed around, the table name only did not yield any values, ALLSELECTED in any form did not yield any values (Yes, that was crazy)....so I'm stumped.
    
    Suggestions? Thank you in advance,
    Tom

Forum Discussion

DAX to create a Trend line?

Related content

Trend Line Equation

How to add Trend line in Line Chart

Trend line values

Trend Lines!

Add trend line to line chart