Forum Discussion
Trend line on chart with small multiples
Hello! I'm trying to place the trend line but I don't know why the option isn't enabled in power BI.
My X-axis data is set to Continuous.
But you don't see the :c option
Hi! Unfortunately, you cannot use the trend line with small multiples. If you remove that the trend line option will return.
4 Replies
- audreygerred
Super User
Hi! Unfortunately, you cannot use the trend line with small multiples. If you remove that the trend line option will return.
- ManichinFrequent Visitor
do we have any alternate solution or visual available to have trend line with small multiples?
- Sawyer33New 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:
- Sawyer33New 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: