By: Scott Murray | Comments | Related: > Power BI
Problem
Can I really make great data visualization reports with Power BI Desktop? What visualizations are available? How can I access my data? How difficult is it to make changes to my reports to make them more user friendly?
Solution
In a previous tip, Getting Started with Power BI Desktop, we described in detail the new Power BI Desktop (AKA Power BI Designer or PBID) that Microsoft has added to the Power BI tool set. That tip was followed by a second tip which reviewed some of the query methods available in Power BI Desktop. Finally, we provided a great tip on extracting data from a website. The great news is that getting PBID is easy as downloading it at: https://powerbi.microsoft.com/downloads.
The current edition of the software is available free of charge and Microsoft has indicated that the tool will continue to be free. Of course that could change. Furthermore to publish the PBID reports to the web, you must have an account setup on the Power BI site and currently sign up for the web publishing only available to commercial email addresses. Before I go too far I need to warn you that the tool is still in its early stages. Sure it works well from my testing, but features are being added and removed every week and every month. Be sure you temper any move to production with the fact the tool set may not scale as anticipated. Enough said. With all these changes and updates, Microsoft is currently releasing an update version of PBID at least once per month, if not more often. You can see the latest version information on the Microsoft Power BI Support page.
Working with the Power BI Desktop Report Design Panel
When you open PBID, the start screen, as shown below, allows you to start the query process via clicking on Get Data. Alternately, if you bypass the start screen, you can click on the Get Data button in the ribbon.
Once you have your data (see previously mentioned tip about querying data sources), you are ready to start designing your report grid or panel. The panel takes on a drop and pin structure which allows a report designer to quickly drop a visualization onto the dashboard / report design panel. These visualizations act as the model and structure for the dashboard based on the data retrieved in the query. As we will show in this tip, these visualizations are NOT your 'father's" Reporting Services report parts, tablixes / matrixes, or charts. These visualization are updated, interactive and their color palette and style are more modern. Furthermore, the module nature of the visualizations allows for customized visualizations (including ones designed by yourself or others). The selection of customized visualizations seems much like an app store effect.
Let us get started with creating our first report. To frame our example, we are using the AdventureWorksDW database as the basis for our queries; these queries have already been completed, and we are now ready to add a visualization. We actually have two methods of adding a visualization. We can either select a visualization from the list of available items, as shown below or we can just start selecting field which will be shown later in our tip.
After selecting the visualization, a blank version of the chart is added to the design grid.
Alternately, the report designer can actually just start selecting the fields that are needed for the visualization. As illustrated next, under the Fields window, all the tables and fields from the query are shown. Simply, click the "> " to the left of the table name to expand the field list. You simply check the box to the left of the field name. You may notice that some fields have a sigma sign, Ó, to the left of the name. That means that based on the query, that field is considered to be measure field and can be aggregated in various ways. In the below example, we selected SalesAmount, TotalProductCost, and EnglishProductCategoryName.
These selections immediately generate a clustered column chart without us even selecting the visualization type.
Pretty cool so far! If we do not like the selected visualization,
we can just select the visualization on the design grid, and then click the desired
visualization in the visualization gallery that we want to switch to.
Switching is that easy with the results shown below.
Now we have some of the basics out of the way, let us talk about some more of the features of the design space. First if your query returns many tables and fields, you can use the Filter content feature to search for only the specific fields.
Furthermore, as shown next, we can add a second visualization, this time a line chart, using the same fields as before. Unfortunately, the line chart does not lend itself well to the just Sales Amount and Product Cost by Product Category. The illustration below displays two neat triangles, but they are not meaningful.
It would be best if we would add a date field, such as year, to display a trend over time. Here is where just "checking" the field may not work as intended. As shown in the next illustration, just clicking on Fiscal Year adds a new value of "count of Fiscal Year". Definitely, this change is not what we wanted.
To adjust these items, we can actually use the visualization properties
panel to make the appropriate adjustments. As displayed below, we
first click on the X next to the EnglishProductCategoryName file to
remove it from the Axis property. Then we drag the Fiscal Year field to
Axis property. We also removed the TotalProductCost field from the
values section.
Now we have a simple line chart which shows the sales by year.
From there we can add back the product category to the legend
property and now, as illustrated in the below screen print, we have a
line graph showing each of the three major product categories by year.
One of the neat things about the pinned visualizations is that by
default all the visualizations are linked together. That means when you
highlight / drill down on one visualization, the same drill down or highlighting effect
is
applied to all visualizations. Thus, as shown in the below
illustration, when I click on the red line in the right hand graph that
is for Clothing, the left hand chart actually also dims the Accessories and
Bikes columns in the chart while highlighting the clothing columns.
This same functionality follows through to any chart added to the
same page only. We can easily add a second page, as illustrated next, which
is a completely separate report grid. However, the filtering and
highlighting impact only those visualizations on the same page; the
visualizations on the separate pages will not be changed based on
the page filtering and highlighting on another page.
The concept we have been displaying above is called "highlighting"; as you may have noticed, the clicking effect "dims" the non-selected items while highlighting the selected items. In addition to highlighting, we can also filter our visualizations at three different levels:
- Visual level filters - these filters limit the data shown only within the selected visualization
- Page level filters - all the visualizations on a particular page have their results limited
- Report level filters - all visualizations on the report have their data restricted by the filter criteria
To apply a visual level filter, we simply select our visualization as shown below and then under the Visual level filter area, click just to the right of the field we want to filter. When hovering to the right of the field, a down arrow appears; click on the down arrow.
Two filter options are available, basic and advanced. The basic
filtering allows you to quickly "check" the values you would like to
filter by whereas the advanced area allows for setting up complex
filtering including "and / or" functionality.
Notice in the below results that only the visualization on the right is filtered by accessories.
To remove the filter, we simply click the eraser button on the filter. Alternately, you can select the "All" checkbox and the filter will be eliminated.
To apply a page or report filter, basically both are applied in
the same way, we first must drag a filter into the filter area.
Now when we select Accessories, both visuals on the design grid now
are filtered to only include Accessories. Notice how the other product
lines disappear completely. I would like to also note one other option
with the page filter; just below the list of available items, you see
"Require Single Selection". This option will thus only allow a single
item in the filter list to be selected (note if none are selected, all
items appear on the page). The report level filter works in the
same way, so we will not cover that item in this tip.
Next we have the ability to change several page formatting items. In
order to change the page formats, you first must make sure you do not
have any of the visualizations selected. As shown below, you click
on the paint brush, step 1, to adjust the page formats. We can change
the name of the page tab, as displayed in step 2. Finally as
shown in part 3, the background color of the page can be changed based
on the selection in the color palette.
Finally, we are going to finish up our tip discussion with some format
changes that can be made to each visualization. Similar to the page
formatting, we must first select the visualization we are working with,
and then click the paint brush. In the below example, we clicked on the
down arrow next to the Legend heading; then the position of the legend
was changed to Bottom and we added a Legend Name of "Figures: ". Notice
how the legend now appears on the bottom of the visualization and is
prefixed with "Figures:".
We can also adjust the visualization's title, title font color,
title background color, alignment, and entire background color, among
other items.
Data labels can also be added to chart, and as illustrated below,
the labels can be customized for color, display units (thousand,
millions, etc.) and precision.
Finally, and probably my favorite formatting option, is the ability
to customize the color of each display instrument (bars on bar chart,
lines on line chart, etc.). First, we click on the Data Colors open to
open up the properties; next, we can hand pick the color we would like
for each data item on our visualization.
This last customization is a great way to help you make your visualization stand out, although the colors I picked above may not be the best examples of "good colors" on charts and graphs. In a future tip or two, I am hoping to cover the publishing process and also cover what you can do once a report is deployed. I am also planning a tip on customized visualizations.
Conclusion
In this tip we continued our review of the new Power BI Desktop Tool. The tool is fun to use and with almost monthly releases, many new and valuable features are to come. The report or dashboard grid allows you to easily drag and drop visualizations onto the design grid. Once, the visualization is added, you can quickly drag fields into the various areas of the visualizations, such as the axis, values (measures ), or legends sections. Furthermore, you can select a particular item on a grid, and PBID highlights that item on the selected visualization and dims any other item on the visualization. The neat thing is that the highlighting and dimming effect is then applied to all the other visualizations on the same page. In addition to highlighting, you can also add report level, page level, and visual level filter; these filters apply only to the appropriate level that was filtered (different from the highlighting effect). Finally, various items at the page level can be formatted to your liking; similarly, each visualization can be custom formatted for background color, legend placement and labels, data labels, and even data color.
Next Steps
- Microsoft PowerQuery https://powerbi.microsoft.com/en-us/
- Read these other Power BI Tips
Learn more about Power BI in this 3 hour training course.
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
