Rock the Mash Pit in Power BI Designer
In a previous post (Power BI Get Data: The Mix-Up before the Mash-Up), I reviewed the options available from a vast number of data sources and ways to tailor those data sets prior to upload in the new Power BI Designer. But now that you’ve got your data ready, where do you start your data mashup in order to build those appealing visualizations that will help the data tell your story? How can you make it “pop”, as we hear so often?
Well, let’s start by asking a few basic questions. These are the same that you would ask in any general inquiry…
- Who? Is it a particular group of users or demographic? Or locations based on countries or regions? Think about how you want to span your data across a set of variables and identify the factor(s) that you want to analyze.
- Where? Or When? Is there a specific time period that you would like to analyze or present? The past fiscal year, or the last five? Or perhaps you want to filter based on location… we’ll review that in a bit.
- What? The most important question – what are you analyzing in your data? The answer to this question varies but is often retrieved numerically. Do you want to look at revenue? Units sold? How you define answers to the previous questions can help give context to this.
For this example, I’ve uploaded several tables from a SQL database that contain music industry information. I’m starting with a blank dashboard, and let’s say I want to see the units sold by different genres. I can easily reference these in the ‘Fields’ menu on the far right and expand each table to reference the columns underneath. To start working with a column of data, check the box to the left and it immediately populates your dashboard. In my example below, I’ve selected the ‘Name’ column from my ‘Genre’ table and the ‘Quantity’ column (the sigma sign next to it signifies that it’s calculating a value) from the ‘InvoiceLine’ table. By doing so, Power BI Designer has intuitively displayed it as a basic table on my dashboard.
This is a powerful feature of the application since it recognizes the data type and knows how to format it based on the visualization chosen. However, we want to use a different chart that’ll better display this data. Luckily, we have a large number of default options available in the ‘Visualizations’ menu on the right. Click on any chart style and your dataset will automatically convert to the style you selected. Consider choosing a visual display that makes it easy for your user to read and interpret. Let’s use a basic bar chart for our next example.
As you can see, it’s very easy to do! Keep in mind that we also can present the same data with different visualizations to emphasize specific variables or relationships/trends in the data. For instance, we can create a tree map using that same data and place that visualization next to our bar chart. This visualization and placement clearly demonstrates how much ‘Rock’ vastly outsells other genres.
But what if we wanted to change the color of the bar graph to emphasize ‘Rock’ as a top-selling genre in the same way that the tree map does? First, click on the bar you want to customize and then select the ‘Format’ icon next to the ‘Fields’ icon below the Visualizations display. Find and expand the ‘Data Colors’ option below and you’ll see the default color listed. By default, this will change the color for every bar. To work with your bars individually, slide the ‘Show All’ option to ‘On’ and you’ll see the full list below. Simply click the drop-down menu next to the one you select and choose your new color. In the screenshot below, I’ve used this feature to match the top four sellers with their corresponding genre in the tree map.
Next, let’s say we want to give our audience the ability to filter these charts by location. Remember the “When?” that we reviewed earlier? Well, let’s take a look at how to apply that, in much the same way that you would apply a slicer in Excel. First, let’s find the right variable – in this case, we have a ‘BillingCountry’ column in our ‘Invoice’ table. I’ll grab that and choose the slicer option from the visualization menu. Now your audience can choose any country in the list and BOTH of your charts will filter for that selection. In this case study, I’ve chosen Brazil.
Finally, let’s add some finishing touches on this visualization and complete our dashboard for a total experience. Make use of the ‘Map’ option to emphasize the relation of total units sold by country. Not to be confused with the ‘Filled Map’ option, this visualization can be found to the immediate right of the ‘Tree Map’ option. Be sure to pull in a location variable as well (since the functionality of the map is dependent upon it) and also a measure for total units sold. Top it off with a simple text box for a title by finding it in the ‘Insert’ section of the ribbon above. After adding this, we can view the finished dashboard and manipulate our data with the filter in real time, as seen below.
Remember, your visualizations will only be as good as your existing dataset. By the time you’ve reached this stage, the hard work of scrubbing and organizing your data should be done. After your data has been transformed, you can take advantage of Power BI Designer’s utility to create stunning visualizations and tell your data’s story in an interactive way.