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 same as using TREND function in Excel?
I can add one to a standard line chart:
But I would really like it on my Line and Stacked column chart:
The formula would need to take into account any slicers which change the date range.
Any thoughts / tips greatly appreciated!
Mark
Solved! Go to Solution.
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] )
Proud to be a Datanaut!
Hey,
depending on the complextiy of the algorithm you want to use, a proper DAX statement can be become very hard, but calculating some kind of moving average might get you started.
Here is a post that might provide some ideas:
https://community.powerbi.com/t5/Desktop/Moving-Average/td-p/43041
Regards
Tom
There are a couple of articles I know of on linear regression with DAX which might cover what you are wanting to do:
https://xxlbi.com/blog/simple-linear-regression-in-dax/
Proud to be a Datanaut!
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
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.
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] )
Proud to be a Datanaut!
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
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.
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
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
At a glance, I can see two fixes to make:
The first few rows of the corrected DAX should look like this:
TempSlope = VAR RowMetric = HospMeasures[Variable_Name] VAR RowProviderID = HospMeasures[PROVDRNO] VAR Known = SELECTCOLUMNS ( CALCULATETABLE ( TR_MeasureTrends, TR_MeasureTrends[PROVDRNO] = RowProviderID , Tr_MeasureTrends[Variable_Name] = RowMetric ), "Known[X]", Tr_MeasureTrends[FY_Trend] , "Known[Y]", Tr_MeasureTrends[Variable_Value] )
...
Does that fix the problem?
Regards,
Owen
Proud to be a Datanaut!
@OwenAuger Yes, thank you very much
User | Count |
---|---|
103 | |
56 | |
55 | |
55 | |
48 |