KPI trend indicators on Core Visuals

With the February and March 2023 Desktop releases comes more features enabling us to enhance our KPI visualisations out of the box. This article talks specifically to the DAX function LINEST() and the new Subtitle and Dividers formatting options.

For a simple enhancement, we can add the divider to KPI Cards, without title and subtitle, and have the colour of these dividers conditionally formatted according to KPI status.

We can use LINESTX() to find the slope of a trend line. If the values are trending up over time, the slope will be positive, if the values are trending downwards over time the slope will be negative. The following is derived from https://xxlbi.com/blog/simple-linear-regression-in-dax/.

Trend =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( Table[Column] ),
            "Known[X]", [Measure X],
            "Known[Y]", [Measure Y]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )
VAR SlopeIntercept =
    LINESTX(Known, Known[Y], Known[X])
VAR Slope =
    SELECTCOLUMNS(SlopeIntercept, [Slope1])
RETURN
    IF(Slope > 0, "trending ▲ upwards", "trending ▼ downwards")

Once we’ve found the slope, we can use additional DAX to create a descriptive subtitle for our KPI chart.

With additional touches, we can conditionally format the data labels on our values to return a specified colour for the last value, and transparent for all others. Alternatively, we can layer another DAX measure that returns only the last value to then plot a point and label at the end of the series in a different colour.

Using LINESTX() again, we can create a new measure to plot the line of best fit. This could also be done using analytics trend line, however currently, there is limited formatting for this option.

By placing a measure that returns the values for a line of best fit in the visual fields well we can format thinner lines.

The title and subtitle can be split across lines using unicode characters, and deselecting the wrap text option.

Title = "Employee NPS" & UNICHAR(10) & CONVERT([Current NPS], STRING)

A target line can be plotted with a DAX measure. To create a data label showing both category and value, a Y-Axis Constant line from the analytics pane can also be drawn, with the line set to 100% transparency, the label set to black.

Error bands can be used from the analytics pane to fill the area between the trend line and target line. An additional area can be plotted on top between values and target line with colour set to white.

Adjusting the spacing above and below titles and subtitles, and adjusting the axes ranges with conditional logic, we can change the alignment of the visuals to make them appear more compact.

Constant lines can also be used to emphasise Y and X axes. With regard to NPS, it makes sense to view the whole range of possible values from -100 to +100