Power BI comes stocked with a great set of visuals that keep getting better (more options to do multiples, please). Even when the default set isn't enough, there is also a great collection of additional visuals you can find in…
The journey continues… Five years on from my life-changing encounter with PowerPivot, my pilgrimage to the Nirvana of Modern Excel continues with some occasional detours. When I first encountered PowerPivot back in 2013, I thought that this tool would obviously…
The Journey Begins I am an accountant fascinated with data. My PowerPivot journey started in 2013 when I encountered the PowerPivotPro blog while googling to find a way to tackle a multi-dimensional financial reporting task. Because of the limitations of…
It's time again for the March Madness tournament and another excellent opportunity to introduce/teach people at your company/organization about Power BI. This article explains one approach for doing March Madness Power BI style, and using everyone's bracket data to teach…
Real-World Dashboard for Disaster Monitoring / Planning / Acting Intro from Rob: It’s all About Action! Informing people is worthless. When working with clients or students, I like to challenge them with this seemingly-controversial statement: Informing people is worthless. Then…
Greetings P3 Nation! I’m here today to talk about maps, maps, globes, charts, topographical representations, and…well frankly I’m out of map synonyms. However there’s at least twice as many ways to visualize data with maps in Power BI as there are…
Do YOU Want To Be A Report Superstar? Hello again P3 nation, today I’d like to drop some reporting knowledge. I’m going to share some of my best practices for Power BI Reporting I’ve developed over the years. As many…
If you want to look for trends based on weekday (Sunday…Saturday) or month-of-the-year seasonality (January…December), Cycle Plots can be a potent visualization tool. Some clever folks thought of this back in 1978, but my education on cycle plots has been from Naomi Robbins’ excellent paper. This question was asked during one of our Q&A sessions for the Online Class (Next Class scheduled for Aug 3-4). In this post I’ll discuss the Cycle Plot and then we would build it step by step using Power Pivot. Here is the end result we will achieve (animated gif):
Cycle Plot showing Weekday values for each Week Number and the Average. A slicer also lets the user select the weekday chart should start at
Looking for Periodic Trends
We’ll use sample data showing eight weeks worth of web traffic and we are looking for trends based on the weekday (Sunday…Saturday).
Try 1: We might plot Visitors by Weekday. While this does show the overall pattern of visitors across weekdays, we have no information of how visitor count is changing over those eight weeks.
Try1: No information on how visitors trend changes over the eight weeks
Try 2: We can plot visitors by date. This shows a trend over time and the cyclical pattern is apparent. However, it is hard to track the performance of a single weekday, say Monday over the eight weeks.
Try 2: Hard to track a single weekday (say Monday) over the eight weeks
Try 3: We plot by weekday but add Weeknum as a series. This has a lot of information coded in the graph, but that is also the problem. Viewer is overwhelmed and it is hard to look for the patterns we want to see.
Try 3: A little too much information
A Cycle Plot would show data for each weekday broken by the Weeknum. With the same data now rendered as Cycle Plot, you can see the trend for each weekday and see them in relation with other weekdays. Insights just start hitting you on the head!
1) Friday is the peak traffic day and Mon/Tue are the low traffic days.
2) Thu shows strong growth, especially in the recent weeks
3) Fri shows steady growth
4) Sunday shows a gradual decline (except for last week) and may need closer monitoring
Building a Cycle Plot Using Power Pivot
Post by Rob Collie
The Models We Build in Power Pivot are the Prime Movers. Visualization is “Just” Where the Information (Output of the Model) Meets the Humans.
A Comment Plucked Straight from My Brain!
Avi’s post last week was deliberately thought-provoking (and to some, perhaps outright provoking, heh heh). It drew a lot of views, shares, tweets, and comments.
My favorite comment, by far, was this one by Andrew. Here’s a slightly condensed version of it:
“Five years from now, I envision a time when awesome visualization tools and incredible and beautiful charts are common and very cheap. Everyone will have those and they will be easy to make. What will still be rare is what Power Pivot does and the role that it plays along with Power Query. The real action is in prepping data and turning it into information that can be visualized… not the actual visualizations. Unfortunately, so many get lost amid a sea of pretty bars on maps and dynamic spider webs…
It’s the model stupid! It’s the ease of crunching numbers and aggregating millions of rows on the fly! It’s the simplicity of turning trash data into sparkling clean information and not having to go through red tape clogged and extremely expensive departments to do it – do it yourself!…
Hear hear, Andrew! Salute! We park our cars in the same garage, as the movie producer said to Christian Slater in True Romance. As I’ve said before, even the phrase “let’s look at the data” sets us up for failure.
Visualization: NOT Unimportant. Just Easier to Replace!
Just Like Light Bulbs: Crucial, But MUCH Easier to Replace than the Wiring.
In short, my observations today come down to these three things:
- RVOE: Replacement Value Over Excel. Excel is essentially free, and is incredibly under-rated as a viz tool. If you measure any Viz tool’s true value through this lens, it’s much harder to justify the price of most of them.
- It’s Relatively Easy for Software Firms to Build a Viz Tool. Compared to modeling and calc engines like Power Pivot, at least. And Power Pivot is the best such engine on the market. So, I think it’s sensible to start with Power Pivot as your core “commitment,” and then pick your viz layer – there are many available, and many more to come as time goes on.
- Don’t buy a “full stack” Analytics Tool Just Because of its Viz capabilities. This is kinda the corrolary to #2, but it also helps us understand why certain “Viz” tools are so stinking expensive.
- My parting thoughts on the ways in which Viz tools ARE important.