A Trend Line is available in the Analytics pane for the clustered column chart and the line chart. The Trend Line is particularly valuable when a chart contains many data points and significant variation exists among the points, making it difficult to observe the trend of the metric visually.

In the following example, a trend line and two additional reference lines (average and constant) have been added to a clustered column chart to provide greater insight and context:

Trend, constant, and average reference lines

The Label Density property of the Data labels formatting card has been set to 100 percent. Additionally, the position property of the data labels has been set to an inside end with a white color and text size of 11. Clear visibility of the data labels for each column, in addition to the two reference lines (Average and Goal), avoids the need to display the Y-AXIS and gridlines.

Excluding the three reference lines from the Analytics pane, the clustered column chart simply plots the Internet Sales Customer Count measure against the Calendar Month Ending Date column. The Calendar Month Ending Date column (for example, 11/30/2017) is required for the axis input in this scenario as both the trend line and the forecast line require either a date or a number data type for the axis. For example, if the Calendar Yr-Mo column was used for the axis (for example, 2017-Oct), both the trend line and the forecast line cards would not appear in the Analytics pane.

The DAX expression used for this measure is included in the Dimension metrics section of Chapter 10, Developing DAX Measures and Security Roles. To ensure that the current month's data does not impact on the trend line, the Calendar Month Status column was used as a page level filter. The filter condition applied (Is Not Current Calendar Month) excludes the latest month from the visual and any other visuals on the report page. Additional information on the Calendar Year and Month Status columns is included in Chapter 8, Connecting to Sources and Transforming Data with M.

With the essential column chart built, the three reference lines can be added from the Analytics pane per the following image:

Analytics pane

As per the preceding image, the Style of the Trend Line is set to Dashed with a transparency of 0 percent. This formatting ensures that the trend reference line can be easily distinguished from other data on the chart such as the other two reference lines. The Combine Series property is not relevant to this visual as there is only one series (Internet Sales Customer Count), and Use Highlight Values is the default setting for calculating the Trend Line.

The numeric symbols (1) next to the Constant Line and Average Line cards denote that a reference line of each type has also been applied to the visual. For these reference lines, a Dotted line style has been used, and custom names have been configured (for example, Goal, Average) to be displayed via Data labels. These two additional lines make it easy for users to identify the columns which are above or below the average value for the visual (89) and the constant goal value of 120.