Dashboard in a Blog (or Two) – Part 2

Welcome back! This is part 2 of our Dashboard In a Blog (or Two) series. If you missed part 1, click here [CD1] to catch up and then come back here to continue with part 1.

Go ahead and open up Power BI and the file you saved in part 1. When it opens, you will see there are three tabs on the left to take you to three different areas. Let’s explore them all.

The first tab is “Report” view.  The second is “Data,”and the last is “Relationships.” Let’s go to the “Data” tab for a moment, and see what we have there.

This may look familiar, as it’s largely the same view that we had for shaping our data. If you find that you missed something when cleaning up the data, some of the editing can be done here. You can also use “Edit Queries” at the top to go back to the Query Builder.

I forgot to delete my VITA and TCE columns, so I’ll simply right-click each of those and select “Delete.” You can also do some quick renaming this way, without having to go back to the Query Builder.

You can also scroll through your data, to see if there are any anomalies. This will become more relevant after you’ve built a report, and you’re trying to understand why things are happening.

Click “Relationships” on the left. This will bring us to an important, but unexciting screen:

So normally, you would pull in several data sets and be able to add to this dataset. For this beginner demonstration, we will not be doing that. Just know that if we were to pull in another dataset such as list of Governors by state or demographics by Zip Code, we could add a relationship and use that additional data in our report. This will be a topic for another day.

Let’s go back to the report tab.

On the right, you will see a variety of ways to visualize the data. Further to the right, you will see all of the fields from our data.

Anything with the sum icon next to it ( ∑) is an aggregation, which can be totaled for graphing purposes. These will make the most sense in combination with something that does not have that icon, so it can group by that. If you start checking boxes, you will see a recommended visualization appear on the report surface. As an example, check “Income Tax” and “State,” and you will see a chart like this:

You can use the corners to widen it if you like, or you can use the scroll bar at the bottom to look through all of the columns.

If you don’t like that visualization, you can simply select another one from the Visualizations section.

Some of the visualizations make more sense than others, depending on the data. It comes down to two questions:

• “What is the story you are trying to tell?”
• “How is the best way to convey that to the reader”

You can click on an empty spot on the report surface if you’d like to create another visualization. You can start either by checking boxes or by clicking the visualization you’d like to see. Let’s try a map (the globe icon, not the filled in map):

Once you click that, you’ll see a number of fields show up to show where you’d like data to be used for the map.

Let’s try dragging “Zip Code” into the “Location” box. Let’s also make the map box bigger, so we can see what’s going on. You may notice an information icon in the top left corner. If you hover over, you will see there’s too much data for it to try and visualize.

We can also use levels to make things work easier. You can drag the “State” into the same “Location” field, placing it above Zip Code.

Now you’ll see the map zooms out and we see a dot for each state.

At this point, we haven’t asked the map to give us any data besides where each state is. So let’s drag “Overpayments refunded” into the “Size” box.

And you’ll see the map has changed. Now we have larger dots for states with more overpayments refunded and smaller dots for less.

If you hover over a dot, you will get a tooltip with more information.

By default, the different charts will filter each other on the same report. Let’s click the “CA” bar on the bar chart of the first report and see what happens.

It filtered the map down to just California. Now in that map, you will notice a few icons on the top when you hover over:

As you hover over these, you will see descriptions.  The two down arrows will drill down to the next level of the hierarchy. Remember how we did State above Zip Code in the Location field for this map? That’s how we can drill down to Zip Code. Let’s drill down, and give it a minute to refresh.

Now you can see, Zip Code by Zip Code, where the largest refunds went. If we had a table that summarized the Zip Codes into something larger (counties, for example), we could add that as another layer of drill down.

In these examples, if you were thinking something such as “Of course California collected the highest income tax, they have a very high population. What about an average amount of tax per return?” Let’s try that next.

We don’t have a column in our dataset for an average per return, but we do have the ability to calculate. Since we want to do this calculation after things are aggregated rather than line by line, we use what’s called a measure.  If you right click the “2014 Tax Data” in the “Fields” section, you can select “New Measure.”

It may look like not much happens, but a formula bar shows up at the top of the report surface:

The language to use here is called DAX. I don’t expect you to learn DAX in this blog post, but you can find a lot of resources around this.  Here, we’re going to keep it simple and just paste my formula:

Tax Per Return = SUM(‘2014 Tax Data'[Income Tax]) / SUM(‘2014 Tax Data'[Number of Returns])

If your column names are slightly different, you will need to update this to match. When you are done, you will have a new measure called “Tax Per Return.”

Now, let’s click an empty space on the report surface, select a pie chart, and then check “State” and “Tax Per Return.”

As you can see, things look pretty even. There are a few larger and smaller wedges, but California isn’t quite so out of proportion anymore.

Learn by Doing

I could write for days about all the things you can try and do with Power BI. However, this blog as well as part 1 should give you enough to get started. Continue playing with this data set by adding more calculations and adding more visualizations to see what kind of insights you can get from this data. Then, try creating your own based on data that is important to you. The best way to learn is by doing.

The best part is that you’re not alone. There are many people who are learning Power BI the same way you are. Using your favorite search engine can help you, but the Power BI Professional Idea Network can also be a fantastic resource. It’s a great place where people working in Power BI can get together to share their triumphs, challenges, recommendations, and questions. Come join your peers on LinkedIn at http://bit.ly/powerbi-pin.