Forum Discussion
Helper VDAX to create a Trend line?
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] )
Super UserHi 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]
)
Hi,
I've tried your exact code but I can't seem to get it to work. What am I doing wrongly? Please help!
I am plotting a Line and Clustered Column chart with "Sum of 'data'[Workers tested]" as the Column value and a created column "Percentage" = DIVIDE ( 'data'[Abnormal Results], 'data'[Workers tested]) for the Line value.
For the shared axis, I've used 'data'[Date of test].
Below is a sample of the data used.
| Date of test | Workers tested | Abnormal Results | % Abnormal |
| 1 Jan 2023 | 100 | 0 | 0 |
| 1 Jan 2023 | 50 | 25 | 0.5 |
| 1 Mar 2023 | 70 | 7 | 0.1 |
| 1 Apr 2023 | 40 | 20 | 0.5 |
- OwenAuger
Hi matthewtay
Thanks for your patience as I have been a bit busy this month!
I have attached a PBIX containing a suggested approach.
- With the LINEST/LINESTX functions now available, I suggest using these rather than the calculation in the post above. For your example, I would use LINESTX.
- To get this to work, you will need Percentage to be a measure. That's why it wasn't appearing in the intellisense options.
Here are the measure definitions in the attached PBIX:
Percentage = DIVIDE ( SUM ( data[Abnormal Results] ), SUM ( data[Workers tested] ) )Trendline = VAR Known = FILTER ( SELECTCOLUMNS ( CALCULATETABLE ( VALUES ( 'data'[Date of test] ), ALLSELECTED ( 'data' ) ), "Known[X]", 'data'[Date of test], "Known[Y]", [Percentage] ), AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) ) ) -- Regression using LINESTX function rather than explicit calculation VAR Regression = LINESTX ( Known, Known[Y], Known[X] ) VAR Intercept = SELECTCOLUMNS ( Regression, "@Intercept", [Intercept] ) VAR Slope = SELECTCOLUMNS ( Regression, "@Slope", [Slope1] ) RETURN AVERAGEX ( -- Since the Y-values are percentages, it makes more sense to average than to sum DISTINCT ( 'data'[Date of test] ), Intercept + Slope * 'data'[Date of test] )Putting this into a visual similar to what you've described:
Hope this helps! Please post back if needed.
Kind regards
No worries! Just glad that you're helping!
Unfortunately my company's IT department has yet to push down the latest Power BI app so I don't have the LINESTX function yet 😞
I've noticed that the main difference is that you've not changed the SUMX function to an AVERAGEX for the last line after the "RETURN" function. I've tried to incorporate this to the previous solution you've provided but to no avail, i still don't get a straight trend line.
Percentage of Abnormal Results = DIVIDE( SUM('data'[AbnormalResults]), SUM('data'[WorkersTested]) )Trend line = VAR Known = FILTER ( SELECTCOLUMNS ( CALCULATETABLE ( VALUES ( 'data'[SubmissionDate] ), ALLSELECTED ('data') ), "Known[X]", 'data'[SubmissionDate], "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 ( 'data'[SubmissionDate] ), Intercept + Slope * 'data'[SubmissionDate] )Unfortunately I'm unable to upload my data as it's my companie's data.
Will be uploading the photo of the graph in a second.
The "Percentage Abnormal Results" is plotted as the yellow line, while the "Trend line" is plotted in the dark blue line. For the life of me I can't seem to figure out how to get it to be a straight line as a trend line should be 😞
Both "Percentage Abnormal Results" and "Trend line" are formatted as "Percentage" under the "Measure tools" tab.
Am I doing something wrongly OwenAuger ?