Power BI: Calculate Slope using Custom DAX

Photo by Choong Deng Xiang on Unsplash

Power BI has a built-in trendline function within the analytics pane that allows you to enhance your existing visual that provides focus on particular insights in the data. However, there might be times when you need a more concise way to represent such trends. We will look at how to calculate the Slope of a trend line and use it to create a trend indicator.

Regression is a simple statistical method to measure the relationship between independent and dependent variables. Let’s begin by creating a measure that estimates average workday lead times for manufactured components.

Build the Date Table
We will build a Date table using a Data Analysis Expression (DAX) to take advantage of some of Power BI’s time intelligence functions. For our purposes, the Date table will depend on our fact table’s ship dates.

The next step is adding a calculated column with a flag for workdays (Mon — Fri) within the Date table.

A simple way in flagging workdays

Calculate Average Lead times
Now we are ready to build our measure. Depending on your specific business logic, consider particular features in your measure. In this model, the components’ “hold release” dates would impact the actual ship dates.

The above DAX creates a calculated column within the fact table which provides a value or the number of dates between two date values. The next step is to create a measure that calculates the average of these values for our visual.

Plot the measure

Add a trend line

Now we move to build a visual indicator of the trend line. The slope calculation is based on the linear regression formula — Y = a +bX.

Where:

• Y = represents the dependent variable
• X = represents the explanatory variable
• a = is the y-intercept
• b = the Slope

The above DAX has three parts. The first part creates a temporary table with the average lead time values and the corresponding dates. In creating the date column in the temporary table (_Tbl), be sure to make the date column numeric. The model used to compute the Slope will only work with numeric data. The second part assigns variables to be used in the regression slope formula shown in the below image (b). The final section returns the slope value based on the model of the Slope.

Taken from https://www.statisticshowto.com/ [1]

Create the Trend Indicator
Now we move to the final element, where we create a simple visual to represent the trend of a data series concisely. Let us create a trend indicator. Also, one that can be used in multiple visuals. The bottom DAX makes the ‘up’ and ‘down’ arrows based on the value of the Slope.

The next DAX dynamically sets the colors of those arrows, again based on the calculated Slope value. For more on Hex-colors, visit the color-hex website for information and representation of those values.

Putting It All Together
We have created the necessary measures to make our final visual. We start off by creating an empty card type visual.

Then we add our arrow measure to the card type visual.

Under the visual tab we will dynamically change the color of the callout value using the DAX we just created.

Visual filtered on a specific component that has a declining historical lead time.

Visual filtered on a specific component that has a increasing historical lead time.

A table visual using the same methods above.

Conclusion
I hope you enjoyed this article, and be sure to give it a thumbs up. The DAX we created was not built for dynamic date hierarchy and will be a topic we explore in future articles. We have yet to explore this technique’s full potential here, so please feel free to use what was described here in any way you can.

Resources

[1] Statistics How To: Linear Regression: Simple Steps, Video. Find Equation, Coefficient, Slope — Statistics How To