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] )
Impactful IndividualHello 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
SlopeUnfortunately 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
Super UserAt a glance, I can see two fixes to make:
- There is no need to use SELECTEDVALUE when defining RowMetric and RowProviderID, as you can access the required values by direct column references. This is by virtue of the row context you have when defining a calculated column. If you use SELECTEDVALUE, you will get blank result as there is an "unfiltered" filter context when defining a calculated column.
- The expression CALCULATETABLE ( ALL(TR_MeasureTrends), ... ) will always return all rows of TR_MeasureTrends, effectively ignoring the filter arguments provided to CALCULATETABLE. Get rid of the ALL and this should work as intended.
I am assuming there are not relationships between the two tables by the way (appeared to be the case from the screenshot).
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