Forum Discussion
Trend line on chart with small multiples
- 2 years ago
Hi! Unfortunately, you cannot use the trend line with small multiples. If you remove that the trend line option will return.
do we have any alternate solution or visual available to have trend line with small multiples?
- Sawyer3327 days agoNew Member
I hate to necro this post, but this question came up to me today and I solved it.
To add a trendline to a visual with small multiples, you'll have to add a calculated field, then place that field to your visuals Y-Axis as an average.
For my dataset: I have a list of recorded Labor Efficiencies. Each row is for a part number, operation, and workcenter, by datetime.
I wanted to have a visual showing the trend of an operations labor efficiency over time, agnostic to workcenter or part number. Additional filters in my code below may be irrelevant to you but I've included in case you find them useful.Trendline Labor Eff by Op =
VAR CurrentOp = MedGrp_OpStats[Operation Description]
VAR TrendData =
FILTER(
MedGrp_OpStats,
MedGrp_OpStats[Operation Description] = CurrentOp &&
MedGrp_OpStats[Labor Efficiency] <> 0 &&
MedGrp_OpStats[Count of OpDesc+PN] >= 10
)
VAR AvgX = AVERAGEX(TrendData, VALUE(MedGrp_OpStats[Actual Finish].[Date]))
VAR AvgY = AVERAGEX(TrendData, MedGrp_OpStats[Labor Efficiency])
VAR Slope =
DIVIDE(
SUMX(TrendData, (VALUE(MedGrp_OpStats[Actual Finish].[Date]) - AvgX) * (MedGrp_OpStats[Labor Efficiency] - AvgY)),
SUMX(TrendData, POWER(VALUE(MedGrp_OpStats[Actual Finish].[Date]) - AvgX, 2)))
VAR Intercept = AvgY - Slope * AvgX
RETURN
Intercept + Slope * VALUE(MedGrp_OpStats[Actual Finish].[Date])
Resulting visual: