Add Trend Line to Column Chart with Categorical X Axis

[yaman123]

Hi all, 

Is there any way to add a trend line to a chart with categorical x axis? Or are there custom visuals which can do this? Its a bit strange you dont have the option to do this in power bi 

Thanks

[Gabriel_Walkman]

Hi!

Could you describe your scenario a bit. Like what kind of categories would you have on your x axis? Its a bit strange to want this.

[yaman123]

I have a date column in the x axis and count of a column in the y axis. When i change the x axis to continuous, the graph comes out in a weird format and with thin lines and spaced out. Whereas if its categorical, it shows correctly. I need to show a trendline but the only way to do this is when you change the x axis to continuous

[v-easonf-msft]

Hi,  @yaman123 

This is by design . If the x axis is type Categorical, you won't get the trend option.As a workaround, you can  create trend lines through dax.

Linear regression = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Table'[Date] ),
            "Known[X]", 'Table'[Date],
            "Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
        ),
        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] * Known[X] )
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 * Sum_X
    )
VAR Intercept = Average_Y - Slope * Average_X
RETURN
    SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )

Please refer to these related threads for more details.

Simple linear regression in DAX 

analytics-with-power-bi-desktop-for-trend-analysis/ 

DAX-to-create-a-Trend-line 

Best Regards,
Community Support Team _ Eason

[yaman123]

Hi, 

Where do i put this measure in the clustered column chart visual? How would i show a trend line on the chart? 

[v-easonf-msft · Accepted solution]

Hi, @yaman123 

You need use  line and clustered column chart visual to replace the clustered column chart visual.

So that you can put this measure to line value.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Hi @v-easonf-msft 

Does this work with Count? My Y Axis is a count of a column. When i add the measure to the chart, it doesnt show as a trend line. Attached image 

[v-easonf-msft]

Hi,  @yaman123 

Could you please tell me whether your problem has been solved?

For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi,  @yaman123 

Yes, the same applies. You need to replace the sum(sales) with count(id) in the original formula . If the problem is still not resolved, please share your sample pbix file for further research.

Best Regards,
Community Support Team _ Eason

[elisebear08]

When you say replace sum(sales) with count(id), do you mean in the estimated sales calculation? Instead of Sum, what if Actual Sales = SUM(Sales[Amount]) was Actual Sales = AVERAGE(Sales[Amount])?