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, Microsoft Power Apps, Power BI

Microsoft Can't Make Your Power BI Reports Accessible Without Your Help

Every once in a while, someone asks a question like “Can Power BI be accessible?” or “Is Power BI WCAG compliant?” It makes me happy when people recognize the need for accessibility in Power BI. (I’ll save the discussion about compliance not automatically ensuring accessibility for another day.) But most people don’t appreciate the answer to either question.

A woman, man, and person in a wheelchair positioned next to the Power BI logo

The answer is that WCAG compliance and accessible design are highly dependent upon the report creator. Microsoft has added many built-in accessibility features such as keyboard navigation, high contrast view, and screen reader compatibility. But they can’t make your report automatically accessible as there are accessibility features requiring configuration by the report designer. We need to set the tab order and alt text and use descriptive chart titles – there is no artificial intelligence to do this for us (yet?). Beyond that, things like color contrast and colorblind-friendly design are almost entirely the responsibility of the report designer.

Accessible design used to be solely the domain of UI developers. But as we democratize analytics to have everyone building reports, we now have to create awareness and a sense of responsibility among Power BI report creators, especially those who don’t consider themselves developers.

There is a similar challenge going on with data security. It used to be that people thought of it as a concern only for the IT department. Now, it is widely accepted that everyone in an organization plays a role in maintaining data security. I hope the same attitude will be widely adopted when it comes to accessibility in data visualization and analytics.

This challenge is present in any low-code environment with users of diverse backgrounds and technical expertise, which means it is relevant to the entire Power Platform and other similar tools. There is a white paper on PowerApps Accessibility Standards and Guidelines that has a great description of the situation.

PowerApps embodies the idea of “democratization of development”—anyone in your organization can quickly and easily create a powerful app and share it broadly. But the app maker has an ethical, and sometimes legal, obligation to support “democratization of usage” as well—any user of your app must be able to use it as it was intended.

Based upon the popularity of the Power Platform, I’d say the democratization of development has been a wild success. But we still have some steps to take to democratize usage. Microsoft is doing their part to make their products accessible and to fix accessibility bugs quickly. Now we need to recognize and honor our obligation to design inclusively.

The Microsoft Docs on accessible report design were recently updated to provide more guidance. I hope you’ll check them out and start implementing the recommendations in your reports.

Accessibility, Conferences, Microsoft Power Apps, Microsoft Technologies, Power BI

I’m Speaking at Microsoft Ignite 2019

I’m happy to be speaking at Microsoft Ignite this year. I have an unconference session and a regular session, both focused on accessibility in the Power Platform.

The regular session, Techniques for accessible report design in Microsoft Power BI, will be Wednesday, November 6 at 2:15pm. In this session I’ll discuss the features available in Power BI for making accessible reports and demonstrate techniques for making your reports easier to use. This session will be recorded, so if you can’t make it to Ignite, you can catch it online.

My unconference session, Accessibility in the Microsoft Power Platform, is a chance to have a discussion about accessibility in Power BI and PowerApps. It will be held on Thursday, November 7 at 10:45 am. Unconference sessions at Ignite include facilitor-led discussion and exercises that encourage audience participation where everyone can share their experiences and opinions. If you will be at Ignite and want to share struggles or successes in improving accessibility or raising awareness of accessibility issues, please join me.

This year at Ignite there is a reservation system for unconferences. You can RSVP while you are building your schedule on the website. Walk-ins will be accepted just before the session, assuming there is room. But please RSVP if you want to be sure to get a seat in an unconference session. Unconference sessions are not recorded, so this will be an in-person session only. But I will post materials through the Ignite website once the session is over.

If you will be at Ignite, please stop by and say hello and meet Artemis the Power BI accessibility aardvark.

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.