Forum Discussion

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

Add Trend Line to Column Chart with Categorical X Axis

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago

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's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
    yaman123
    Icon for Post Partisan rankPost Partisan
    5 years ago

    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's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        yaman123
        Icon for Post Partisan rankPost Partisan
        5 years ago

        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