Introduction
Welcome back for Week 9 of Workout Wednesday, Power BI edition. If you’re joining us for the first time, welcome! You can jump right in this week, where we’ll be exploring our data in Power BI.
For this week’s challenge are going to use anomaly detection and forecasting to gain a deeper understanding of what’s going on now and in the future in the Great Lakes. For those of you participating in the Tableau challenge, you may remember doing something similar in Week 5.
This challenge may also look similar to the Power BI Week 7 challenge, as we’re building off of David’s report.
Requirements
- Find a Power Query transformation that will change the individual lake columns into rows. You’ll end up with only three columns loaded into your data model (this comes straight from Week 7)
- Add a line chart that displays the average coverage by year, forecasted out to the year 2030.
- Add a second line chart that also displays average coverage by year showing anomalies at 75% sensitivity, explained by Lake.
- Formatting is totally up to you! We’ve been loving the creativity that the #WOW2021 community has been producing – keep it coming!
- Answer the following questions:
- What is the projected average ice coverage in 2030?
- Which lake contributed most to data anomalies?
Dataset
You all made some beautiful reports in Week 7 using data on maximum ice coverage from NOAA’s Great Lakes Environmental Research Laboratory. If you participated in Week 7, use that report as a starter and we’ll build on it this week.
If you did not participate in Week 7, that’s okay! You can get the raw data from Data.World and use Power BI’s built-in Data.World connector, or alternatively get it in Excel format from GitHub. A lake can have ice coverage ranging from 0 (no ice) to 100 (frozen over).
If you’re only interested in visualizing data and not transforming it for this challenge, use this PBIX from GitHub (requires Power BI Desktop December 2020 or later).
Share
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. 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.
Hi
I cannot make the anomaly explanation by Lake work. 🙁
I’m using a proper date formatted field on the Axis. The anomalies show ok, but with no explanation by Lake. I’ve dragged Lake to the ‘explain by’ field, and applied successfully.
I’ve watched the uTube video a few times now and just cannot make it work.
I can send my model if required
Can you help please? This is driving me mad!
J
Anomalies ‘explain by’:
Only works on https://app.powerbi.com/
and does not work when testing in Desk Top mode.
Anybody else having problems with Anomalies – ‘Possible Explanations’ not working!
Can anybody help?
Are the answers for the challenge as below
1. 55.07
2. Erie
Hi,
My power bi doesn’t shows the option to turn on anomalies? can any one help?
Hi Sunay,
What version of Power BI Desktop are you running? The feature was released as a preview feature in November of 2020, so you’ll need to be on that version or newer. You may also need to ensure that the feature is enabled in the preview features in options and settings. If you’re on a recent version and the feature is enabled, ensure that the value on your X axis is formatted as a date. Let us know if this helps! You can also check out this tutorial for more details: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-anomaly-detection.
Hi Jason – apologies for the very delayed response. Were you ever able to sort this one out? I’d love to help you troubleshoot.
Hi Shannon, I’m on the June 2021 version but there’s no anomalies on my preview features as well.
HI Leah,
You don’t need to enable Anomalies from preview features list. Its now added in the main build of Power BI
Following this challenge and watching the answer video, hoping to learn what I’m missing.
My line chart in the shaded area of the forecast is a straight line and doesn’t match the variance in the walk through video. What am I missing?
Hi Will! Is your confidence interval set to 95% and seasonality set to auto? That’s my first guess – double check those settings. Hopefully this helps!
Thanks Arka! Yes – I failed to mention earlier that the feature is no longer in preview – it’s now generally available and you should be able to find it in the analytics pane 🙂
I am facing the same issue here, Can you please tell me how you fixed this?
You need to filter “All Lakes” from your Report.
Same with Week7, filter in a way that Lake is not All_Lakes
To get the forecast work try to filter out “all lakes” and the year 2021. Worked for me.
how to count projected ice coverage?
Hi Shailee! If you hover over the data point on the line chart in the projected years, you’ll see that the value for Forecast 1 is 55.1. That’s the value you’re looking for 🙂
Solution to this challenge https://youtu.be/o91_5tU6OY4?si=9ddwKEYcaKvk_wxh
I have a column of dates in Power BI, but the data type of this column is currently set to Whole Number. The data represents years, ranging from 1973 to 2020, such as 1973, 1974, etc. When I try to change the data type to Date, it only shows a single year throughout the column. How can I correctly change the data type from Whole Number to Date in Power BI?
Hi Humayun,
The date data type expects something that looks like a date format, so you’re exactly correct, you won’t be able to set that column as a date. Depending on what you need to do with the data, you could add a column in either Power Query or using DAX and set the date to 1/1/1973 (for example). I hope this helps!
Shannon