Learning Microsoft Power BI

Chapter 4. Let’s Make Some Pictures (Visualizing Data 101)

Previously we discussed the basics of the Power BI user interface, touching on the Report, Data, and Model views, as well as Power Query. In the preceding chapter, we imported our first set of data from our Cool School University dataset.

In this chapter, we’re ready to go over some basic ideas about why we visualize data. Then, using our data from the preceding chapter, we’ll do a walk-through of the multiple visuals available by default in Power BI.

Why Visualize Data?

Imagine this. The year is 1950, and you’re an accountant at a small manufacturing firm in the Midwest. You served in World War II as an accountant at the Department of War, ensuring those war bond funds were spent efficiently to help the Allied war effort.

To do all your work, you had a ledger book. That was it. So, every day you’d log your transactions from one account to another, making sure all the money was where it was supposed to be.

One day, the floor manager of the plant comes to the accounting office to ask you a few simple questions. “Hey, I’m curious. Which of our products actually makes us the most money? What about on a per unit basis? And are they the same product?”

Now, as a 1950s accountant, would you willingly just hand over your ledger book? Absolutely not. Never. The ledger book was an accountant’s lifeblood, and back then someone would have had to pry it from your cold, dead hands before you’d part with it.

What you would do is, using the data compiled, put together a table that would show the list of products, the total profits by product, and the profit per unit for each product. One might call that table a very simple data visualization.

Now, for sake of argument, let’s say that a different accountant (not you, you’re too smart) would consider just handing over their ledger book, saying, “Sure. You can figure it out. Here’s the record.”

Would that make sense? No. It doesn’t make sense for two reasons. First, the floor manager and their team aren’t going to be as familiar with the data. They didn’t compile it. They didn’t do all the math, and they’re likely not accountants. So, even if an accountant did hand over the ledger book, the floor manager and their team might have a hard time following the logic. Second, the accountant had already done all this work, so why would they ask someone to do it again? That’s just not efficient.

Let’s get back to our example and the data. We discover that widget A has the highest total profitability and widget B has the highest profitability per unit. The follow-up question becomes, “Is that true just for this year or is it true historically?”

I could create an even more complicated table. I could have columns for each year and do the calculation by product and put together a nice matrix. While some people can look at the matrix and follow along, some of our audience now isn’t getting the impact of the data.

I want to make the data as comprehensible as possible, so I create a simple graph with an x-axis for year and a y-axis for profitability. I plot the points and draw a very simple line that shows the trend for those products, separating them into their own graph for clarity.

Which do you think will better stick in people’s minds? The matrix or the graphs?

Answer this question for yourself. You’ve likely been in a meeting or presentation where someone showed data in the form of a long, drawn-out table, and maybe that’s when you felt yourself start to drift off a bit. Another presenter showed data using graphs and images. Which one did you find more engaging?

I’m willing to bet that the overwhelming majority of people find the graph approach much more engaging. I feel confident of that because we are inherently drawn to stories. We’re drawn to stories because we’re more naturally able to take a story and learn lessons from it than we can from a list of facts.

The fact that we find it easier to identify with these anecdotes over hard data is taken advantage of. For example, it is possible you’ve had a bad experience with an undocumented immigrant. That anecdote left an impression and when a new organization pushes individual stories of misdeeds, it becomes easier to latch onto that qualitative story over the reams of data that shows that undocumented workers commit crimes at a far lower rate than native born citizens.

We have all sorts of biases that we must contend with, and we have to be able to view and understand the facts to overcome them. But if the choice is between data that tells no story and a story that serves our bias, the story that serves our bias will win.

Data visualization fundamentally uses that phenomenon of storytelling as teaching to our advantage. Data visualization uniquely triggers all three parts of the ancient Greek modes of persuasion: ethos, pathos, and logos. The art of doing the work to create the visualization demonstrates your mastery of the data, giving you an ethos credibility. Showing the data in the form of images that are easily understood and enable you to tell a story is the core of the pathos, or emotional appeal. Finally, the data itself, assuming it’s been collected and managed properly, should only tell you the truth. At its core, the data elements are the facts, and the facts make the argument’s logos appeal.

We visualize data because we are storytellers. We don’t tell our stories from the whimsy of our imagination. We tell our stories from the facts, establishing our credibility and understanding of those facts. From there, we make that story as digestible as possible. And, again, the story is based on thousands or millions or billions of data points, not just a few anecdotes, which lends to credibility.

Remembering the adage that a picture is worth a thousand words, a good graph can be worth millions or billions of rows of data. You are a storyteller, and you’re the best kind of storyteller, one who makes sense of what’s really happening. Whether finding the answer or finding the truly important question that needs to be answered, storytelling with data visualization can help you get there.

So, let’s talk about the visualization tools Power BI gives us to tell that story with our data. Think of these tools as the alphabet. Once you get familiar with them, you’ll learn how to make words from them, then get to the point where you are telling stories with them, and, with time and practice, you’ll figure out how to use each visualization to maximum effect. With that in mind, let’s talk about where that all begins, the Visualizations pane.

The Visualizations Pane

The Visualizations pane is your go-to spot for adding visualizations, modifying them by adding the requisite data points, formatting your visualizations, or adding additional analytics capabilities that Power BI supports in some of these visuals. The Visualizations pane consists of three major parts: Fields, Format, and Analytics. See the recently updated Visualizations pane in Figure 4-1.

Figure 4-1. The Visualizations pane is our visualization toolbox. Don’t leave home without your toolbox!

Fields

It’s important to note that each visualization will look different from other visualizations in the Fields area because they all accept different inputs. A map visual isn’t going to be the same as a bar chart, which isn’t going to be the same as a matrix. If you have fields in a visual already and choose to change the type of that visual into something else, Power BI will try its best to reallocate the selected fields and measures to the new visual; but it’s not guaranteed to work as you intended. You can change the type of a visual already on the canvas by selecting that visual and clicking a different visualization in the Visualizations pane. That’s it.

Common elements that will show up in many visuals in the Fields pane include Axis, Legend, and Values. An axis defines how you are categorizing your data. A legend splits the data into subsections and highlights those distinctions. Values are the actual values you want to aggregate across those groupings.

In Fields, there will always be a section at the bottom for “Drill through” options. This cool feature allows you to take a visual and apply the filters currently on that visual to another visual on another page, taking you directly to that report page and its set of visuals. “Drill through” is a really great way to take insights from one portion of your report to another and to keep that context in sync.

Format

In previous versions, the Format pane was depicted by a paint-roller icon. Now, when a visual is not selected, it appears as a paintbrush over a page. But when a visual on the canvas is selected, it looks like a paintbrush over a bar graph. Format allows you to customize the look and feel of a given visualization to meet your specific needs.

Much like with Fields, the eligible options under Format are contextual to the visualization being modified. Many options are available, and if you’ve ever used PowerPoint, many should feel familiar to you. Each section in Format has an arrow next to it that allows it to be open or collapsed. This can be helpful for navigation purposes. Some features under Format have distinct functionality, but for the purpose of this exercise, focus on the thought that Format is where you go to make your visualizations really pop.

Analytics

The Analytics functions, accessed by clicking the icon of a magnifying glass looking at a graph, don’t work on all visualizations, and it’s important to note that no custom visuals can leverage the Analytics area. However, for visuals where Analytics does work, you can use this functionality to add in constant lines for comparison, minimum value lines to identify when something is below a given threshold, a maximum line for the obverse purpose, an average line, a median line, a percentile line, or a trend line, or even do anomaly detection.

There’s so much functionality under Analytics, but using it effectively is context dependent, so don’t be afraid to try things to see what works or doesn’t for you and your project.

Visual Interactivity

Before we get into details of actual visuals, we should discuss one of the most powerful features on a Power BI report page: visual interactivity. By default, any visualization you put onto a report page can filter any other visual on the page. This enables a user to quickly get to specific combinations of data they could be looking for.

In Figure 4-2, you see a simple report page with two visuals. One is a map visual showing the number of students I have in my class from each state, and the second is a simple bar showing the average score across all assignments.

Figure 4-2. This shows a combination of data detailing where our students came from. I think our student from Hawaii is lost.

When I click any of those bubbles in the map, the average score visual will change to reflect the specific subgroup I’ve selected from the map visual. Looking at the map visual, I can see many of my students are from Indiana. But I want to see how my students from other states might compare to the average, for instance. When I choose the bubble in Michigan, I get the result shown in Figure 4-3. For demonstration purposes, I’ve captured the image while I was hovering over the result for the column graph to make it clearer to you.

Figure 4-3. Here we demonstrate cross-filtering across visuals. We can see our students from Michigan perform above the average of the rest of the class.

You can see in the map visual, all the other bubbles are transparent now, indicating I’ve selected one. Now, on the right, you’ll see that the column graph shows a new result. It shows a highlighted column, and behind that highlighted column, you can see the original result. From this, I can quickly see that the average score for my Michigan students is higher than my average for the entire group of students. You’ll note, though, that it doesn’t immediately give me the original average without the Michigan students. This type of cross-filtering, which highlights a value, doesn’t remove the selection from the original value.

So, this compares the Michigan student average to the entire student average, which still includes the Michigan students. If we wanted to do something a little different, we could do so with some custom measures, and we’ll discuss that more in our chapter on DAX fundamentals.

Now, at times you might not want a certain visual to cross-filter or be cross-filtered, and not all visuals have the same types of cross-filtering display options. When you have a visual selected, in the ribbon under the Format tab, you’ll see a button called “Edit interactions.” Unlike other buttons we’ve discussed, this is either on or off. When it’s on, you will see in the corner of each visual on your report page some different icons; again, not every icon will be on every visual.

Let’s take, for example, the column graph from our preceding figures; with “Edit interactions” on and the map visual selected, we would see three icons, as shown Figure 4-4. The icons are very small. I wish Microsoft would make these interaction icons a little bit more obvious, but they’re not. When I talk about an icon being selected, it will show as being “filled in,” as compared to being see-through. In the example, you can see the middle button is filled in, so that’s the type of cross-filtering that is done by the map visual against the column visual.

On the left is the Filter option. This would strictly filter the result to show only the filtered result; so, in this case, we would see only the Michiganders’ result, instead of the result against the entire average. The second option is the Highlight option, which does what you saw in Figure 4-3. The final option is None, which means that the selected visual will not cross-filter that visual at all.

Figure 4-4. From left to right: Filter, Highlight, and None

Column and Bar Charts

Column and bar charts typically have a very simple x-/y-axis design. Take the values and compare them across two dimensions, and garner some insight from that. Some charts allow us to add in a second y-axis to use when we want to overlay one value against another. In the column and bar chart category, Power BI has the following visuals by default (in their order in the Visualizations pane, left to right, top to bottom, skipping over visuals that aren’t relevant to this portion of the text):

• Stacked bar chart

• Stacked column chart

• Clustered bar chart

• Clustered column chart

• 100% stacked bar chart

• 100% stacked column chart

• Waterfall chart

Stacked Bar and Column Charts

The stacked bar and column charts accomplish the same goal with different verticality. Which one is your x-axis, and which one is your y-axis? As a good rule of thumb, use bar charts when comparing against discrete values and use columns when you’re measuring against continuous values like time, for instance. This isn’t a hard-and-fast rule, though.

For this chart, I’m going to look at the difference between students for whom this is their first class in the department and those for whom it is not. I’d like to know their average score, and I’d like to see how many office hours were attended by each group.

We can see both the bar and column examples in Figure 4-5. Note that for these two charts, the values you lay on top of each other form a summation of whatever values make up the visualization—in this case, the average assignment score and the sum of each group’s office hours. This gives us a combined value that allows us to quickly compare multiple columns and find results that may not be intuitive on the surface.

Looking at both charts, they share the same inputs from the Visualizations pane. You define an axis. You can add a legend as a category to further subdivide the bar or column, the values you want to review, small multiples, and tooltips.

The tooltip is what you see when you hover over a certain part of the visual with your mouse cursor. When the tooltip is empty, Power BI will put together a list of values into the tooltip based on what’s in the visual. Think of it as a quick table you can make appear for a given set of data to help you read the visual. You can also put things into the tooltip that aren’t necessarily in the visual.

Figure 4-5. Bars versus columns

Clustered Bar and Column Charts

A clustered bar or column chart takes the values and separates them into discrete items against an axis, as opposed to aggregating them together. In the stacked charts, we basically got one bar or column combining all the values together. In the clustered chart, we get a separate bar or column for each value across our x-axis.

In the example in Figure 4-6, I want to see if there is any real discrepancy between the average scores by ethnicity and by age. I want to see each ethnicity highlighted separately, but I want to see how the ages compare against each other and how the average score compares on a more apples-to-apples basis. I can do this by separating out the values for the average age and average score. Figure 4-6 shows us what this looks like in both bar and column form.

Figure 4-6. Clustered charts split out, as opposed to pulling together

This set of charts does a nice job of showing that the way you present data matters. If we look at the top portion of Figure 4-6, it’s easier to see the difference in the average age that exists in each ethnic grouping. I think this is a little harder to see in the column example. Both the column and bar charts here do a good job of showing the difference in the average score, though.

Sometimes changing a value from an x- to a y-axis can make a big difference, and remember that by default axes are dynamic. That’s something for you to consider when choosing bar versus column charts or if you feel the need to manually set axis values (which you could do in the fields area for this group of visualizations under the relevant axis category).

100% Stacked Bar and Column Charts

The 100% stacked bar and column charts take a given set of values across dimensions and figure out, for that total, what percent of the total belongs to each specific section of the grouping. This normalizes the result across categories because it’s based on percentages instead of absolute values.

For example, in our class we have way fewer student athletes than students who are not athletes. If we were to look at the total number of office hours attended by each group, the nonathletes would just look like a much larger group. However, what if I’m trying to figure out if a particular assignment got more office hours in one group than another? That’s an example where normalizing the data can come in handy, as we see in Figure 4-7.

Here we have 13 assignments with office hours attended. We can see that our student athletes attended office hours for only assignments 5, 6, 7, and 12. Assignment 12 noticeably had 20% more office hours than the other assignments. We see that, for the nonathletes, the distribution is fairly uniform across assignments. Not perfectly uniform, but close.

If we were teaching this class, this could lead us to ask some additional questions. Was there an issue in sporting schedules that made getting to office hours more difficult? Was the extra interest at the end of the semester because the student athletes weren’t performing well, or was it just coincidence? Some of these questions I can answer from the data, and some of them I probably can’t, but hopefully you can see some example questions I might want to ask as I develop my report or my research into the topic.

Figure 4-7. Percentage-based comparisons can be useful; however, with many categories it can become unreadable

Small Multiples

Small multiples is a feature that lets you split the visual into slices while maintaining common axes. Say I wanted to divide this into four quadrants using the student athlete identifier and the gender flag. I could see this broken out for all four combinations—not an athlete and female, not an athlete and male, is an athlete and female, is an athlete and male—while keeping my core axis value. That gets us the view represented in Figure 4-8, which also contains an example of a tooltip value that isn’t elsewhere in the visual (in this case, the average age for that grouping of data).

Figure 4-8. Small multiples are a great example of what puts the Power in Power BI

Let’s take a second and talk about the inferences we can draw. We know that there are no male athletes for whom this is not their first course. We know the inverse is true of female athletes. We can see the average score is higher across all groups for those people for whom it is not their first class. And, generally, we know that those who were taking their first class in the department attended more office hours, but those office hours didn’t quite get the first-timers up to the level of the more experienced students in the department.

Waterfall Chart

A waterfall chart compiles by a given category how a value changed over that category until it shows you the final total value. This can be useful when you have a combination of positive and negative features that contribute to a total, so that you can break them out and compare them to each other. We don’t have negative grades for anyone, so Figure 4-9 demonstrates a single-directional waterfall chart showing the calculation of office hours attended by AssignmentID before showing the total number of office hours attended for the semester.

Figure 4-9. Sometimes it’s OK to chase those waterfalls!

Line and Area Charts

Line and area charts, like column and bar charts, have a very x- and y-axis focused theme. The difference is often that it can be easier to see trends or do comparisons with lines or areas, seeing where data in a time series might overlap or observing where certain categories might intersect.

Also, Power BI has a set of charts that combine column charts with line charts that we will discuss in this section. Again, the list of visualizations in this category is read from left to right, top to bottom, skipping visualizations that aren’t in this section:

• Line chart

• Area chart

• Stacked area chart

• Line and stacked column chart

• Line and clustered column chart

• Ribbon chart

Line Chart

A line chart isn’t really that different from a column chart, except that it’s easier to see trends with lines than it is to see them with columns. Line charts also work best with a continuous axis because when there’s a gap, the line chart will break the line and pick it back up for the next value in the axis series. If you look at the options in the Visualizations pane for column and line charts, you’ll see that they look incredibly similar, except that the line chart has one additional option for Secondary values. That section is there in case we want to do two y-axes against each other, which is the unique feature about line-type charts. This can be useful when you want to compare trends of two values, but they might have very different orders of magnitude.

A good business example of this is comparing my average product price to my total profit. Average price for my products is going to be lower than my total profit (or at least it should be, or you have much bigger problems than this book can really solve.) If I were to put those values on the same axis—say, profit in millions and average price in the hundreds—the average price line would look virtually flat and near zero compared to millions in profits. However, if I put them on different axes, with each y-axis measuring its own order of magnitude, we can solve that problem and better see the trends together.

In Figure 4-10, I’ve put a line chart together showing average score by assignment ID alongside the student ID count for each assignment. Even in the example provided, the number of students doesn’t reach the level of the average score on any assignment, even the lowest-scoring one.

Figure 4-10. What happened on assignment 14? Stay tuned to find out!

Area Chart

An area chart has all the same category options as a line chart. Think of an area chart as a line chart with the values below the line filled in. Voila, area chart! The area chart does two things in my mind that provide good use cases. The first use case is when you have data that changes over time, and you want to give the reader a sense of the proportion of that change. A good example of this is looking at the change in population over time.

You can also use an area chart to see more easily where two or more values overlap, or—usually the more interesting case to me—where they don’t. Figure 4-11 is the same as Figure 4-10, except it’s an area chart. At the very end of the chart, for assignment 14, you’ll see that the area that’s filled in under the student count line isn’t also filled in by the average score because the average score dropped. That filled-in area serves as a great visual indicator that something there is abnormal or may be worth further investigation.

Figure 4-11. That little triangle at the end begs so many questions!

Stacked Area Chart

A stacked area chart is a bit different from an area chart in that it not only shows you the individual values in the area, but also creates an aggregate of the values of the area, allowing you to see both the individual parts of a total and the total at the same time.

Imagine you are in a business that has multiple subscription models: silver, gold, and platinum. The stacked area chart can be great in an example like this because it will show you the silver, gold, and platinum areas on top of one another, allowing you to see the combined subscriptions as well as their component parts.

Figure 4-12 demonstrates a stacked area chart so that we can see our average assignment scores per assignment by the number of years a student has been in school. Looking at AssignmentID 3, we can see that, for some reason, students with one year in school really struggled with this assignment. Maybe they’ve gotten used to sleeping in already?

Figure 4-12. Students with one or two years of attendance seem to struggle on this one

Line and Stacked Column Chart/Clustered Column Chart

We discussed stacked and clustered column charts earlier in this chapter. What does adding a line do for these charts? Quite a bit, as it turns out! The ability to take a column chart and then overlay a line value on a different axis can really expand analysis.

For example, in Figure 4-13, I break down my average score by race for the line, but use a clustered column on the total sum of all the scores. This view tells me very quickly that White people are the majority in my class, but the Black or African American students have the highest average score. However, the inclusion of the second y-axis also tells me that the difference in the average score between my racial groups isn’t that large, ranging from about 76 to a high of 80. If I were a teacher who was concerned about my course being pedagogically attuned to the needs of all my students, I would take this result in a very positive light!

Figure 4-13. Sometimes just an extra line and axis can tell us so much

Ribbon Chart

A ribbon chart is somewhat like an area chart, but what it really excels at is showing how things rank against each other. You can see how the ribbon flows across the category. The tooltips for ribbon charts are also exceptionally detailed when hovering over the “transition” part of the ribbon. That’s what I call the portion of the charts that aren’t the columns being compared.

You can get a good idea of a simple ribbon chart in Figure 4-14, where we’re looking at the rank of the average score for those who attended office hours versus those who didn’t. We can see at the beginning of the semester that those who didn’t attend office hours were doing better, but then that changes in Week 5, so that those who were attending office hours were doing better. What are some of the questions this raises in your mind?

Figure 4-14. I want to know when office hours make the most impact

Donuts, Dots, and Maps, Oh My!

This next section of charts covers a bit of a broader spectrum than the previous two categories. I like to group these things together because they don’t necessarily fit as neatly into other categories for me. They tend to be about demonstrating the effect of categories rather than summarizing a value across a category. Put another way, what does a given category contribute to something? That brings us to the following list of charts, again listed in their order in the Visualizations pane, reading from right to left, top to bottom:

• Funnel chart

• Scatter chart

• Pie chart

• Donut chart

• Treemap

Funnel Chart

A funnel chart is a flexible visualization. At its core, a funnel chart is about comparing a collection of data to another collection of data to see how close or far away it is.

One of my favorite examples of a funnel chart was from when I worked in operations analytics in a call center. That call center did telephonic coaching, and it was important to know how many times we would successfully connect or engage. I would use a funnel chart to show off our total eligible population, and then the number of people we engaged with once, then twice, then three times, etc. Each successive reach was harder to get, and you couldn’t reach someone for a third time unless you’d reached them a second time. This allowed us to quickly understand our conversion rate at each level of customer engagement.

In our example in Figure 4-15, we’re looking at a different use case for the funnel: a presorted list of average scores by student. Pay special attention to the tooltip, the information in the black box in Figure 4-15. It shows the percentage of the highlighted value in comparison to the top value. Mr. Schmitz, with an average score of 85.36, has an average that is 92.28% of the top score (Montegalegre’s) and an average score that is 97.47% of Boomhower’s.

Figure 4-15. Visualizing rank

Scatter Chart

The scatter chart is one of the most useful and most maddening visuals in Power BI. I’ll be blunt: it’s fickle. There are way too many ways to get an error out of this visual when you are constructing it. If you have data that you can represent nicely in X and Y pairs, the scatter chart is fantastic. One of its other uses is to display how values change over time. Just as an additional note, your y-axis must always be numeric; it cannot be categorical. Your x-axis can be either numeric or categorical.

In the example in Figure 4-16, we are looking at our average score by assignment between student athletes and nonathletes. What we have added that is unique to the scatter chart is the Play axis, which allows us to cycle through some categorical filtering in sequence to see how these values change over time. This can work really well with continuous data that uses dates or, as in this case, I’m using the assignment IDs, which are numerical, to play through the change in assignment order.

Figure 4-16. Moving pictures. They’re weird, but I bet they’ll be big someday.

Pie and Donut Chart

Oh, I can hear the screaming of finance professionals everywhere, “Jeremey! Why are these together? They’re totally not the same thing!” I want everyone to be clear on this. A donut chart is a pie chart with a hole in the middle. They do the exact same thing, which is demonstrate the percent of a total value that represents a certain category.

Now look at me, and by look at me, I mean stop reading, lift your eyes to the sky and hear my disembodied voice in your head. Pie and donut charts are overrated and overused. Does this mean they are bad? No, of course not. However, naturally it is easier to compare lines and bars against each other than it is to compare “areas” of values.

Does your pie chart have 74 values that it is comparing? That’s bad. Is it a real sugar crème pie from Zionsville, Indiana? Then it’s good. Is your donut made of some vegan, gluten-free flour substitute from a back alley in Portland, Oregon? Probably not good. Seriously, go to Coco’s. Voodoo is overrated. Figures 4-17 and 4-18 show good pies and donuts versus bad ones.

Figure 4-17. Good pie, good donut. Why? Because they’re both readable.

Figure 4-18. Don’t do this! Here’s an example of pie and donut charts that are not readable. As the kids say, “Y u do dis?”

Treemap

The treemap is the donut chart for the 21st century, which is to say, it can be used well and can be destroyed by too many categories filling it up. However, one advantage the treemap has over its pie and donut counterparts is that it is easier to tell the difference between big squares and little squares. I like to use treemaps as a cross-filter on many report pages where categories and subcategories exist naturally to highlight specific subsections of data for more analysis.

A larger treemap like the one in Figure 4-19 is helpful for identifying large groups at a glance. In this case, we are looking at combinations of gender and ethnicity to see which groupings attended the highest number of office hours. We can easily see females attended more hours than males, and we can see which ethnic groups attended those hours.

Figure 4-19. Group and Detail divisions make treemaps shine

Map Visuals

Map visuals do exactly what you think they do: they align data with geographic categories to show you details about data across those categories. Pretty straightforward. Power BI has several map visuals that can help you get to where you want to go.

The basic map visual offers the ability to define a location or to offer data points for latitude and longitude to define your map parameters. The filled map is basically the same as the map, except it fills in the relevant geographic categories, as opposed to putting in size-based dots.

By the time you get this book, the shape map visual might finally be out of beta (where it has been for years). The shape map does not use Bing or Azure to power it, but it uses custom TopoJSONs to define the map’s shape and structure. A TopoJSON is a special file format that contains semistructured geographic data. If you are in a place where you need a custom map, check to see if you have a TopoJSON built in-house or find one of the many great resources online to get a TopoJSON of the map you need.

The Azure map allows you to define latitude and longitudinal boundaries that can be passed to TomTom to generate a map much like the shape map visual. With map visuals, my suggestion is to try each map visual and see which one works best for you and your needs.

The “Flat” Visuals

Flat visuals exist to display simple, straightforward information that you want your reader to see. These visuals generally do not cross-filter others, but are cross-filtered themselves, with one obvious exception: the slicer visual. These visuals may seem simple, but they often add that magical, extra little bit of context to a report that turns the data into a story that makes sense. They do so by highlighting those specific things that you really want your audience to understand. In this category, we have the following visuals:

• Gauge

• Card

• Multi-row card

• KPI

• Slicer

• Table

• Matrix

Gauge

Gauges are one of the oldest forms of data visualization we have. They’ve been used in machines and engineering for most of the 20th century. A gauge fundamentally tells you how a value is doing compared to its minimum and maximum values alongside a target. The gauge is helpful for setting targets and seeing quickly if you are above or below that target value. It can also be useful when you want to keep a value in a specific range, not too hot or not too cold.

In the example in Figure 4-20, we compare the average score with our goal average score measure.

Figure 4-20. Gauge has been around; gauge has seen some stuff. That’s because gauges are among some of the earliest data visualizations.

Card/Multi-Row Card

The card visual is one of the simplest visuals in Power BI. It’s also one of the most self-explanatory from its name. When given a value, it takes that value and puts it into a square card with a brief note on what it is that’s being viewed.

For me, the card does two things well. First, in its simplicity, it’s easy to understand what you’re reading. Combine this with cross-filtering, and the card visual can quickly highlight a specific value for any combination of data easily. Second, it helps the storytelling process by drawing readers to specific values you think they should care about, either because they’re important on their own or because they provide necessary context that makes the other visuals on your report page more meaningful.

The multi-row card visual is like the card visual’s cousin whom your aunt and uncle dote on constantly at Thanksgiving, but you’re not actually sure they’re all that great. The multi-row card puts, unaspiringly, multiple values into a single card-like space. If all the values are aggregations, it will look fairly clean. However, if you have aggregations and then some dimension that categorizes those aggregations, the multi-row card will create multiple rows on the card, detailing the aggregated values for each categorical value.

In Figure 4-21, we have a simple card on the left and two multi-row cards on the right: one with only aggregations at the top, and sliced by StudentID on the bottom to tell the difference more easily. Note how the second multi-row visual also has a scroll bar to help navigate the visual because it’s now longer than the assigned space.

Figure 4-21. You get a card, you get a card, everyone gets a card! This shows the difference between a card and a multi-row card.

KPI

The key performance indicator (KPI) visual is one of those things that on the surface sounds great, but inevitably leads to a place of frustration. It’s not as bad as the scatter chart, but the KPI visual is not intuitive at first.

There is an indicator field, and this is what you are actually measuring. There’s a trend axis, which is how the visual will display the results on an x-axis that isn’t displayed. Then there’s the target goal, which we should be either higher or lower than.

A classic example of a KPI use case is something like current year revenue versus previous year revenue by fiscal month. In our example, we are looking at our average score compared to our goal grade average of 75. The visual then shows how we did compared to that goal.

The infuriating thing about the KPI is that it always displays the value for the last value in the axis, regardless of what happened before it, even though it will visualize those values in the chart portion. You’ll notice in Figure 4-22 that I’ve left the Filters pane open so you can see what the visual looks like in two scenarios: one where I ignore Assignment 14, which is special, and one where I do not, so you can see the difference in the way the KPI visualizes those results.

Figure 4-22. Sometimes I wish I could give the KPI visual a red card

Table/Matrix

So, I’m going to apologize, because this is one time I’m going to deviate from the order and skip over Slicer. I’ll come back to it after the table and matrix visuals. These are easy to understand if you’ve used Excel or Google Sheets or looked at a database table.

The table is exactly what it sounds like. No bells and whistles here. The table visual, in the Visualizations pane, has only one insert, and that is into a values field. Everything is a field (column), and all the values are assigned to that column. It might seem counterintuitive to have a table visual in a data visualization tool, but the table visual can provide some more specific detail that can offer extra context.

In many scenarios, I might prefer a table to a multi-row card for highlighting specific data points. The other cool thing I’ve done with table visuals is put them at the end of reports to make for easy data extraction for analysts who might want to use the data of that table for other analysis.

The matrix is more akin to a pivot table in Excel, with the ability to have multiple levels of row features to drill into. Matrices can also be overused, but when used to highlight specific sets or combinations of data, they can help illuminate specific items for readers or provide extra context to analysts who can figure out what questions they need to go answer quickly.

For many years, exporting data from a matrix visual would lose its matrix formatting, as Power BI would put it into a table first and then export it. However, that’s not true anymore, so if you have a need to export data in a matrix format, maybe for a presentation or something, you can do that as well. Figure 4-23 shows a table on the left and a matrix on the right.

Figure 4-23. Fine, you want tables? Have some tables.

Slicer

I saved the slicer for last because its functionality is fundamentally different from every other visual we have reviewed, but that doesn’t mean it’s less important. A slicer takes the visuals on a page and filters them in alignment with the selected value(s) on the slicer.

A slicer isn’t too different from having a column selected via the “Filters on this page” button in the Filters pane. What is different, though, is that as a visual on a report page, you can edit how it interacts with every other visual on the report page by using the “Edit interactions” function on the Format tab of the ribbon; and slicers can be synced across pages of your choosing using the “Sync slicers” pane. This can make slicers much more flexible and more intuitive for your report readers. This also gives you, as the author, another chance to identify which dimensions you feel are important for helping your readers understand how they should be thinking about the data.

In Figure 4-24, you can see the slicer visual selected in the bottom-left corner, using the assignment ID. Note how the values change when I select different combinations of the assignment ID I want to look at.

Figure 4-24. Slice, slice, baby!

Conclusion

Yes, the Visualizations pane has a few other visuals, but those aren’t really visuals for the 101 section. If you’re able to do R or Python scripting, you already know how to use the R and Python visuals, and we’ll discuss the AI-powered visuals a bit more in Chapter 7. Power Apps, Power Automate, and Paginated Reports are a bit beyond the scope of this book, but plenty of resources are available if you have an interest in those topics.

In this chapter, we’ve gone through the basics of visualization in Power BI and walked through many of the visuals that are available right out of the box in Power BI Desktop. And we’ve discussed some of their use cases. In many of the visual examples, we used a simple measure, the average score, to use as a point of reference, but how did we make that measure? That’s where DAX comes into play, and that’s what we’ll be moving to next. Excited? You should be.