Conferences, Data Visualization, Microsoft Technologies, Power BI, SQL Saturday

New Power BI Report Design Pre-Con in 2020

I’m excited to announce that I will be offering a full-day pre-con about Power BI report design in the coming year called Bookmarks, brain pixels, and bar charts: creating effective Power BI reports. For a full session description and prerequisites, please visit the session page.

Screenshot of the eventbrite page for the pre-con at SQLSaturday Austin

I built this pre-con to help people better approach report design as an interdisciplinary activity where we are communicating with humans, not just regurgitating data or putting shiny things on a page. There are many misconceptions out there about report design. Some people see it as just a “data thing” that only developers do. Many BI developers avoid it and try to focus on what they consider to be more “hardcore data” tasks. I often hear from people that they can’t make a good report because they aren’t artistic. This hands-on session will dispel those misconceptions and help you clarify your definition of a good Power BI report. You will see how you can apply some helpful user interface design and cognitive psychology concepts to improve your reports. And you’ll leave with tips, tricks, and a list of helpful resources to use in your future report design endeavours.

Your report design choices should be intentional, not haphazard or just the Power BI defaults. We’ll review guidelines to help you make good design choices and look at good and bad examples. And we’ll spend some time as a group creating a report to implement the concepts we discuss.

Basic familiarity with Power BI is helpful for attendees. If you know how to add a visual to a report page, populate it with data, and change some colors, that’s all you need. If you feel like you lack a good process for report design to ensure your reports are polished and professional, this session will share an approach you can adopt to help accomplish your design and communication goals. If you feel like your reports are luckluster or not well-received by their intended audience, join me to learn some tips to improve. If you are a more experienced report designer and you want to learn some new techniques and see the latest Power BI reporting features, you’ll find that information in this session as well.

So far, I’m scheduled to present this session at two SQLSaturdays in Q1 2020:

SQLSaturday Austin – BI – February 7, 2020. Please register on Eventbrite.

SQLSaturday Chicago – March 20, 2020. Please register on Eventbrite.

SQLSaturday pre-cons are very reasonably priced. This is a great way to get a full day of training on a low budget! I hope to see you in Austin or Chicago.

Accessibility, Data Visualization, Microsoft Technologies

How a new custom PowerPoint template is helping us to be more effective presenters

DCAC recently had a custom PowerPoint template built for us. We use PowerPoint for teaching technical concepts, delivering sales and marketing presentations, and more. One thing I love about working at DCAC is that each of the 6 consultants is also a speaker at conferences. So we all care about making presentation content understandable and memorable.

In addition to the general desire to be an effective presenter, I have a special interest in accessible design. I speak and consult on accessibility in Power BI report design. So I feel a responsibility to try to be accessible in all areas of visual design. In addition, I know that making things more accessible tends to increase usability for everyone.

PowerPoint templates in general reduce manual efforts to assign colors, set fonts and font sizes, and implement other design properties such as animations. I’m all for making a decision once, templatizing it, and making it easy to instantiate. But most of the templates available within PowerPoint and sold online don’t follow good presentation design practices. That is to say, the formatting gets in the way of the content.

Having a template made

Anyone can make a PowerPoint template. You just have to learn how master slides work. Lots of marketing and graphic design companies offer to create PowerPoint templates. But they are mostly concerned with staying on brand or making something shiny. And people make presentations because we want to communicate information. I wanted our template to make it easy for us to follow some good design practices, including accessibility.

So I reached out to my friend Echo Rivera who has her own company, Creative Research Communications, that specializes in making visually engaging presentations about highly technical topics. I came across one of her tweets a couple of years ago and visited her website. Some of the things I have learned from Echo’s courses and blog posts include:

Echo worked with me to make a template that followed good design practices but was flexible enough for a variety of content and audiences. And we also made the template fairly accessible! I’m pleased with the way our template turned out. A template can’t do everything for you, but it can give you a good start.

The process of presentation template design

I found the process of designing the template interesting, so I thought I would share a bit about that.

First, I sent Echo some samples of the presentations we give and some ideas on a color palette. We met to discuss our corporate personality, our presentation needs, and our goals for the template. Then a few members of the DCAC team helped me pick some potential images for the title slide.

Next we picked icons and colors for sections that we thought we would commonly include in presentations. We stuck with a white background on some content slides to make it easier to use images and diagrams from online (so we wouldn’t have to expend effort removing image backgrounds when they weren’t transparent). It also made it easier to choose colors with good contrast.

Once we got the full draft of the template, the team reviewed it and tried it out on some slides. And because things were going too smoothly, we decided we didn’t like the image we selected for the title slide, and chose another one. We made a few adjustments to default font sizes so that slides would fit our company name and slide headings with long technology names on a single line. We also adjusted the position of the slide heading text on slides with the line and icon at the top (shown below) so the text didn’t sit directly on the line.

We made a version of the slide template that has a small single-color logo on each slide. I plan to use this for sales slides and other situations where we need to ensure our name is on a slide when taken out of context (like a screenshot of a single slide). But the logo is small and not distracting so it doesn’t negatively impact design.

We made the template expecting that team members would adjust the master slides and use the provided multi-purpose layouts as needed for each presentation. We will change icons and use blank slides to add diagrams and big images. We also have multiple options for title and thank you slides.

Benefits of our new PowerPoint template

There is a lot to the new template, and examples below are shown without a ton of background explanation. But I couldn’t write this blog post and not show some examples, and I didn’t want this post to turn into a novel.

Our slides use color contrast to create visual interest while staying close to our corporate color palette.

The spacing and font sizes allow for about 6 lines of text max in an all-text slide. Our default font size for text content is 32pt.

Content slide with large text and clean formatting
Content slide from Meagan’s presentation on accessible Power BI report design

We used icons and color to denote sections in our presentations.

I think the best thing that this process has done is encourage us to use less text and more images. Check out the recently created slides from one of John’s presentations below.

My checklist for accessibility in visual presentation design includes:

  • font sizes no smaller than 24pt
  • color contrast that meets WCAG standards (3:1 for large text, 4.5:1 for smaller text)
  • avoiding using color as the only means of conveying information
  • avoiding color combinations that are problematic for people with color vision deficiency

Echo worked patiently and diligently with me to measure and adjust colors to meet these criteria. It was great to have someone that understood these goals.

Feedback from other team members

To be honest, I think it took some time for the template to grow on people. But most of us have warmed to it. If you are used to small images, lots of text, and extensive use of clip art and smart art, switching to the template can be a bit difficult. But that’s kind of the point.

Echo gave us some videos on how to use the template and some slide makeover examples when she delivered the template, and I asked the team to watch this TEDx talk. That reinforced the design goals and choices.

I asked the team for comments, and this is what they said.

“I like having a professional-looking, themed template that isn’t just something out of the box that looks like everyone else’s. The template, plus the helpful background info force you to think about what you are puking onto a single slide. It will lead to some semi-major rewrite/re-evaluation of about every presentation I do.”

Kerry

“The template, in part with its notes and reminders, helps me to ensure that I put the proper amount of context on a given slide.  It’s a balancing act with font size and content.  If I have to reduce the font size below 32 pt and I still have content to provide, it goes on another slide.”

John

Final thoughts

Will every slide we make be perfect and beautiful? No. But this template will help us avoid common mistakes like having too much text on the slide, and it provides some built-in accessibility without us having to remember to make adjustments.

I really enjoyed working with and learning from Echo, and I recommend her for your presentation design needs. I want to note that she prefers that you take her training before she does custom design work for you so you have a mutual design foundation to build upon. But her courses are great, so that’s not a hardship.

It’s also a privilege to work with people that share and support my goals of effective presentation delivery and accessible design. PowerPoint templates are part of the branding and marketing collateral in an organization, and I love what this says about DCAC.

Data Visualization, DCAC, Microsoft Technologies, Power BI

Power BI for Communication and Marketing

We often focus on deep analysis and insights generated by machine learning when we talk about Power BI these days because it’s super cool and very fancy. But I think it’s important to remember that you can also use Power BI for simple communication of data. As humans, we are hard wired to process visual information faster than text alone. And it’s often more efficient and concise to convey information in a visual rather than text. We can show in one image what it takes paragraphs to describe. Outside of historical analysis and operational reporting, we can use data visualizations as an engaging way to convey simple facts in a communication or marketing effort. Many people do this with infographics.

Visualization can make simple numbers and facts more memorable and engaging. I enjoy using Power BI for this marketing communication purpose, so I made a report about the people I work with at Denny Cherry & Associates Consulting. I knew most of them before I started working at DCAC, so I was already aware that they were exceptional. But now you can see it, too, with our new Power BI report located at https://www.dcac.com/about-us/learn-more-about-us.

Screenshot of the home page of our About DCAC Power BI report

Data is some people’s preferred language. We can use it to discuss specific points as well as broader trends and comparisons. I have a sticker on my laptop that says “Please Talk Data To Me” for a reason. I can make sweeping statements about how my team is full of accomplished consultants, speakers, and authors. But it is much more impactful when you see the numbers: 27 Microsoft MVP awards and 8 VMWare VExpert awards over the years, a combined 113 years of experience with SQL Server, 86% of us having been a user group leader.

My Surface Pro tablet with a “Please Talk Data To Me” sticker on it

And because Power BI is interactive, you get to interact with my report, choosing which categories you want to learn more about. There is also a little guessing game built into the report. (Edit: Although there is currently a Power BI bug that I had to work around, so it’s helpful to know that the answers are limited to values 1-2500). But that is a blog post for another time, and for now you get a hint as to possible answers.) If I had just told you these stats, you might have listened, but you probably would have tuned out.

Our team even had fun browsing the report, seeing our collective achievements, and guessing who provided which answers.

Building the report

It was quick and easy to use Microsoft Forms to gather the data and then dump the responses to Excel. From there, I made a quick Power BI data model and put together my visualizations. The report is embedded on our website using a Publish to Web link. A few people have said they would like to do something similar for their organizations, so I’ll offer a couple of tips:

  1. Make sure the questions you ask aren’t violating any HR rules, and that employees know that some or all questions are optional. For example, I asked personal questions in the Fun Facts section about number of children employees have. This question was optional and our team felt comfortable answering it.
  2. Try to group your questions into categories so you can easily the separate sections and pages like I did. Then you can associate a color with each category.

If you do make something similar, I’d love to see it. Tweet me a link or screenshot at @mmarie.

Data Visualization, Microsoft Technologies, Power BI

Check Out the Updated Violin Plot Power BI Custom Visual

I wrote about the violin plot custom visual by Daniel Marsh-Patrick back in February. I thought it was a good visual then, but version 1.3 has recently been released with some nice enhancements.

First, the violin plot is now a certified custom visual. This means that it has been tested by the Power BI team to ensure it meets certain requirements, one of which is that the visual does not access external services or resources. You can be confident your data isn’t being sent externally when you use the violin plot.

As for the functional enhancements, a new legend has been added. This is a great addition to make the chart clearer and more easily read, especially for audiences that may not be familiar with how the violin plot works. The customizable legend calls out what markers are used for mean, median, and quartiles.

Violin plot with the new legend

Another good enhancement is the new column option for the combo plot. It allows you to have your plot show as a range column chart where the bar spans from the minimum value to the maximum value for each category. I chose to show only the mean and median in the example below, but you can also add quartiles.

Violin plot using the new column plot type

The barcode plot also has a nice enhancement in the tooltip. Now when you hover over a bar, you can see the number of samples with the highlighted value.

You can check out Daniel’s blog post to see the full list of enhancements for this release. Tweet me if you make something cool and shareable with the violin plot in Power BI.

Data Visualization, Microsoft Technologies, Power BI

Turning a Corporate Color Palette into a Data Visualization Color Palette

Last week, I had a conversation on twitter about dealing with corporate color palettes that don’t work well for data visualization. Usually, this happens because corporate palettes are designed with websites and/or marketing collateral in mind rather than information graphic design. This often results in colors being too bright, dark, or dull to be used together in a report. Sometimes the colors aren’t easily distinguishable from each other. Other times, the colors needed for various situations (main color, ancillary colors, highlight color, error color, KPIs, text, borders) aren’t available in the corporate palette.

You can still stay on brand and create a consistent user experience with a color palette optimized for data visualization. But you may not be using the exact hex values as defined in the corporate palette. I like to say the data viz color palette is “inspired by” the marketing color palette.

I asked on twitter if anyone had a corporate color palette they needed to convert into a data visualization palette, and someone volunteered theirs. So this post is my walk-through of how I went about creating the palette.

Step 1: Identify a Main Color

There is often a main color in the corporate color palette. If that color is a medium intensity color, I usually include that color in my color palette as is. If it is excessively dark, light, or gray, I’ll either tweak the color a bit or use the second color in the color palette.

Step 2: Choose a Color Scheme

Next, I need to decide what kind of relationship the other colors will have with the main color. In other words, I have to decide what type of color scheme I want to use. I tend to go for monochromatic or analogous color schemes. Complimentary color schemes can be difficult, depending on your main color. I generally try to stay away from using reds and greens together in the same palette because it’s hard to stay colorblind-friendly and because the primary colors together can make it feel like a Christmas or kindergarten theme. I often try to reserve reds and oranges to draw attention to specific data points, but that isn’t a hard and fast rule.

I need 2 – 4 ancillary colors to go with my main color. I rarely need to use all 4 colors together in one chart, but there are some cases such as line charts with 4 series where that will be necessary. People can preattentively distinguish up to about 7 colors at once, so I need to use fewer than 7 colors in a single chart. If I encounter a situation where I feel like I need more than 4 colors together, I re-evaluate my choice of chart type and my use of color within the chart.

Also, I want the colors to be roughly the same level of brightness and intensity. Most importantly, the colors need to be easily distinguishable from each other.

RGB Color Wheel

Step 3: Choose Highlight and Error Colors

We often need to draw attention to specific data points to indicate that they require attention. This is usually because a value is outside of the expected range. KPIs are common in Power BI reports, I need to make sure I have a color to indicate “bad” statuses. I also like to have a highlight color that doesn’t necessarily mean “bad”, just “look here”. These highlight and error colors need to be noticeably different from my other colors so that they draw attention to the data points where they are used.

Step 4: Add Border and Background Colors

I like to add grays and browns to go with my color scheme. I’ll use them mostly for borders, grid lines, text, and light background shades. But also, I want to make sure I have 8 colors in my palette. If I have fewer than 8 colors, Power BI will add colors from the default palette at the end of my colors to fill out the full 8 columns.

Color Palette Creation Example

The original corporate color palette that I was given had a lot of colors.

Primary Corporate Colors
Secondary Corporate Colors

The primary colors go all the way around the color wheel. I definitely don’t want to use them all together. The secondary colors have the beginnings of a monochromatic blue palette, an analogous blue/green palette, or an analogous orange/red/purple palette.

I don’t need all of these different hues. I need 8 medium-intensity colors. Power BI will add black and white and provide the shades and tints for me.

Main Color

I’m keeping the main color as it is. It is bright and saturated enough to not be dull/boring and also not so bold as to leave no room for bolder colors to be used to highlight specific data points.

Chart using the main color of my palette

Color Scheme

I choose an analogous color scheme, which means I pick colors that are next to my main color on the color wheel. Since blue is my main color, I stick with cool colors for the ancillary colors.

Main color plus 3 ancillary colors

I want my 4 colors to be easily distinguishable from each other, and I want them to be roughly the same intensity and brightness.

Highlight and Error Colors

I’m adding yellow and red to my palette. The yellow can be a generic highlight color as well as a “caution” color. The red can be my “bad” color. I’m checking that my colors are easily distinguishable for various types of color vision deficiency.

I confirm that my highlight and error colors are easily distinguishable from the other colors for the most common types of color vision deficiency. I can also see here that my second and fourth colors look a bit similar on the deuteranopia line, so I’ll have to be careful how I use them together, perhaps switching to a shade or tint of the fourth color if needed.

Border and Background Colors

Now I add my grays and browns to use for formatting. This completes my color palette.

Full color palette for Power BI Theme

Power BI Theme

I can take the hex values for my colors and drop them in the color theme generator on PowerBI.Tips to get my JSON theme file.

{
"name":"Analogous",
 "dataColors":["#00aaff", "#00c2b2", "#213dca", "#7514ff", "#ffd500", "#ff002b","#768389","#987665"]
 }

When I import my theme file into my Power BI report, I get the additional tints and shades from the colors I provided.

Next I try out my new color theme in a report to see if I need to tweak any colors. This is the true test. The colors may look great in little boxes, but they might need to be altered to work on a full report page. The shade of purple that I used originally (not shown in this blog post) was a bit too intense compared to the other colors, so I replaced it with a slightly muted tint that better matched the other colors. That is the type of thing you will notice when applying your theme to a report. Don’t get too stuck on finding the exact perfect colors. Colors look slightly different on different screens. Just make sure nothing is inadvertently distracting.

Helpful Color Tools

I’m currently using https://color.mediaandme.be to create my color palettes. It’s free, and it allows me to add many (> 6) colors to my palette. Other benefits:

  • It shows me what all the colors look like together
  • It provides a colorblindness simulator
  • It lets me easily tweak hue, saturation, and brightness
  • It generates a link for the color palette I create so I can easily share it with others for feedback

When I need ideas for how to tweak a color, I use https://www.colorhexa.com. I picked the gray color in my palette by getting the grayest tone of my main color from ColorHexa.

Further Reading on Color in Data Visualization

Tiger Color: Basic Color Schemes – Introduction to Color Theory
Maureen Stone: Expert Color Choices
Billie Gray: Another post about colours for data visualisation. Part 3 — DIY Palettes
Christopher Healy: Perception in Visualization

Data Visualization, Microsoft Technologies, Power BI

Storytelling Without Data?

There are many great resources out there for data visualization. Some of my favorite data viz people are Storytelling With Data (b|t), Alberto Cairo (b|t), and Andy Kirk (b|t). I often reference their work when I present on data visualization in the context of the Microsoft Data Platform. Their work has helped me choose the right chart types for my data and format it so it communicates the right message and looks good. But one topic I have noticed that most data visualization experts rarely address is the question I get in almost every presentation I give:

How do I tell a story with data when my data is always changing?

If you are a BI/report developer, you know this challenge well. You may follow all the guidelines: choose a good color palette, make visuals that highlight the important data points, get rid of clutter. But what happens when your data refreshes tomorrow or next month or next year? It’s much easier to make a chart with static data. You can format it so it communicates exactly the right message. But out here in Automated Reporting Land, that is not the end of our duties. We have to make some effort to accommodate future data values. Refreshing data creates issues such as:

  • We can’t put a lot of static explanatory text on the page to help our audience understand the trends because the trends will change as the data is refreshed. Example: “Sales are up year over year, and the East region is the top contributor to current quarter profit” is true today, but may not be true next month.
  • My chart may look good today, but new values may come in that change the scale and make it difficult to see small numbers compared to a very large number. Example: A bar chart showing inventory levels by product looks reasonable today because all products have a stock level between 1 and 50. But next month, a popular new product comes in, and you have 500 of them, which changes the scale of your bar chart and makes every other product’s inventory super small and not easy to compare.
  • I can’t statically highlight outliers because my outliers will change over time. Example: I have a chart that shows manufacturing defects by line, and I want to highlight that the dog treats line has too many defects. I can’t just select that data point on the chart and change the color because next month the dog treats line might be doing fine.

How do we form a message with known metrics and data structure but without specific data values?

When people have asked me about this in the past, I gave an answer similar to the following:

Instead of a message about specific data values, I consider my audience and the metrics they care about and come up with the top 2 or 3 questions they would want to answer from my report. Then I build charts that address those questions and put them in an order that matches the way my intended audience would analyze that information. This might include ordering the visuals appropriately on a summary page as well as creating drillthrough paths to more information based upon items and filters selected to help my user understand the reasons for their current values.

While this isn’t horrible advice, I felt I needed a better answer on this issue. So I sought advice from Andy Kirk, and he responded brilliantly!


To the issue of situations where data is periodically refreshed, I see most encounters (ie. the relationship between reader and content) characterised less by storytelling (the act of the creator) and more by storyforming (the act of the reader). 

Andy Kirk

Storyforming

Andy went on to explain, “What I mean by this is that usually the meaning of the data is unique to each reader and their own knowledge, their own needs, their own decision-contexts. So rather than the creator ‘saying something’ about such frequently changing data in the form of messages or headlines, often it might be more critical to provide visual context in the form of signals (like colours or markers/bandings) that indicate to the reader that what they are looking at is significantly large/small/above average/below average/off-target/on-target/etc. but leave it up to THEM to arrive at their own story.”

Then he gave an example: “I find this context a lot working with a football club here in the UK. Their data is changing every 3-7 days as new matches are played. So the notion of a story is absent from the visuals that I’m creating for their players/management/coaches. They know the subject (indeed better than me, it’s their job!), they don’t need me to create any display that ’spells out’ for them the story/meaning, rather they need – like the classic notion of a dashboard – clear signals about what the data shows in the sense of normal/exceptional/improving/worsening.”

Andy also agreed that a key aspect of storyforming is that interactive controls (slicers, filters, cross-filtering capabilities) in your report consumption tools give the reader the means to overcome the visual chaos that different data shapes may cause through natural variation over time.

Less Eye Rolling, More Storyforming

If you are a BI developer and have rolled your eyes or sighed in frustration when someone mentioned storytelling in data visualization, try thinking about it as storyforming. Make sure you have the right characters (categories and metrics) and major plot points (indicators of size, trend, or variance from target). You are still responsible for choosing appropriate chart types and colors to show the trends and comparisons, but don’t be so focused on the exact data points.

Many reporting tools (including Power BI) allow you to perform relative calculations (comparison to a previous period, variance from goal, variance from average) to dynamically create helpful context and identify trends and outliers. In Power BI, there are custom visuals that allow you to add dynamically generated natural language explanations if you feel you need more explanatory text (ex 1, ex 2, ex 3). And Power BI will soon be getting expression-based formatting for title text in visuals, which can also help with providing insights in the midst of changing values.

But mostly, try to design your report so that users can slice and filter to get to what matters to them. Then let your users fill in the details and meaning for themselves.

Accessibility, Data Visualization, Microsoft Technologies, Power BI

Power BI Now Has Keyboard Accessible Visual Interactions

The March 2019 release of Power BI Desktop has brought us keyboard accessible visual interactions. One of Power BI’s natural strengths is that you can click on a data point within a visual and have it cross-highlight or cross-filter the other visuals on a page. But keyboard-only users weren’t able to use this feature until now. This greatly raises the accessibility of the Power BI report consumption experience.

Below is a demonstration of interacting with a visual using keyboard commands. Notice how I can select specific data points within the line chart, and the other charts on the page filter based upon the selection.

Keyboard commands can now access visual interactions

If you are a person that prefers to use a keyboard over a mouse, this might also be something you want to try. Relevant keyboard commands include:

  • Ctrl + right arrow: Move focus into the chart area of the visual
  • Tab or Arrow keys: navigate between data points (or legend items in a chart that contains a legend)
  • Enter or Space: select a data point within a visual
  • Ctrl + Enter or Ctrl + Space: select multiple data points within a visual
  • Ctrl + shift + c: clear all selections

I think this was the last big missing piece of keyboard accessibility. I’m excited to see its impact on users. If you try keyboard accessible visual interactions, or are taking advantage of keyboard accessibility in Power BI in general, please let me know how you are liking it. Tweet me at @mmarie or send me a note via my blog contact form.