For Week 7 we tried our hand at Linear and Non-Linear regression with the Deneb Custom visual. Since then a new DAX function LINEST was announced. This week we will be utilising the LINEST function to determine trend for use in smart commentary. Also a useful trick for supplementing KPI visuals with trend indicators.
- Obtain the dataset from https://www.kaggle.com/datasets/rtatman/chocolate-bar-ratings
- Model and load data
Power BI Desktop
- Plot ratings by cocoa percentage
- Create a measure to count number of ratings
- Using LINEST create a measure to return the slope of the regression line of ratings by cocoa percent
- Using LINEST create a measure to return the coefficient of determination of the regression line of ratings by cocoa percent
- Create a measure that concatenates the Count; Slope and Coefficient for use as a subtitle or footer on the plot
- Using Cohen’s (1988) guidance on effect sizes, create a measure that returns “small”; “moderate” or “large”, where coefficient is <=0.02, moderate <=0.13, large >= 0.26 respectively
- Create a measure that returns “negative” if the slope is negative, “positive”, if the slope is positive
- Create a measure that returns “down” if the slope is negative, “up”, if the slope is positive
- Use these measures to describe your plot in a text box. Add values to add the measures amongst your text within the text box.
- Create a measure to list the top 3 countries whose ratings improved with cocoa percentage, where n>10 (count of ratings is greater than 10)
- Create a measure to list the top 3 countries whose ratings decreased with cocoa percentage, where n>10 (count of ratings is greater than 10)
- Add these measures to a narrative.
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
3 thoughts on “2023 Week 11 | Power BI: Determining trend with LINEST”
I couldn’t find the solution file on the link you provided. I would be grateful if you could please re-share the file. Thanks
It’s up now 🙂
Thank you 🙂