2023 Week 11 | Power BI: Determining trend with LINEST

3 Comments / Power BI, Workout Wednesday / By

Introduction

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.

 

Requirements

Power Query
  1. Obtain the dataset from https://www.kaggle.com/datasets/rtatman/chocolate-bar-ratings
  2. Model and load data
Power BI Desktop
  1. Plot ratings by cocoa percentage
  2. Create a measure to count number of ratings
  3. Using LINEST create a measure to return the slope of the regression line of ratings by cocoa percent
  4. Using LINEST create a measure to return the coefficient of determination of the regression line of ratings by cocoa percent
  5. Create a measure that concatenates the Count; Slope and Coefficient for use as a subtitle or footer on the plot
  6. 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
  7. Create a measure that returns “negative” if the slope is negative, “positive”, if the slope is positive
  8. Create a measure that returns “down” if the slope is negative, “up”, if the slope is positive
  9. Use these measures to describe your plot in a text box. Add values to add the measures amongst your text within the text box.
  10. 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)
  11. 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)
  12. Add these measures to a narrative.

Dataset

The dataset this week can be viewed https://www.kaggle.com/datasets/evangower/chocolate-bar-ratings

Share

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.

Solution

Solution File available for download via Data Stories Gallery

3 thoughts on “2023 Week 11 | Power BI: Determining trend with LINEST”

  1. Maazi

    Hi Kerry
    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

Comments are closed.

Scroll to Top