For this week we are building a stacked bar chart on https://Charts.PowerBI.Tips. While you can build this type of chart within Power BI Desktop this video is more of an example showing how you can build a similar custom visual chart. There are two areas that i’d like to point out.
When working with a chart you can add a Legend to a shape object (2:40)
Adding a grand total requires an additional Glyph (3:14)
While these two tips are pretty simple it really does help you when building more complex custom visuals.
Watch the Tutorial
Download this Custom Visual
If you liked this visual and want to download it, head over to this repository to download.
Learn More About Custom Visuals
We have been working hard to help you learn how to make custom visuals. Check out our full YouTube Playlist to learn more about using Charts.PowerBI.Tips.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
The more you work with Power BI Desktop it is more than likely you will find some tool limitations that impact your overall design pursuits. As I have worked with data visualization software, I find there is always a balance between what I want to make and what is possible. The more you become familiar with your visualization tool, the better you think of report designs that are both beautiful and feasible. One such design style that I use is to limit the use of slicers on the report page as much as possible. My solution for removing slicers is adding a stacked bar chart or a stacked column chart. The chart can be used as a slicer because you can click on the data bars and filter the page of data. One of the visualization limitations I’ve had to work around was the ability to make a stacked bar chart with long y-axis titles. In order to overcome this limitation we need to fire up our creativity to figure out another way to more accurately control the y-axis labels and the associated bar chart.
There are two main issues we will need to solve:
First issue, when you have text along the y-axis in the stacked bar chart, the text becomes truncated. See below.
Stacked Bar Chart
Note: All the text next to each bar is truncated if the text is to long. This can be fixed by extending the visual to a ridiculous length, as illustrated by the following picture. While this solves the text issue, this totally defeats the purpose of this visual, provide a “slicer” that can be used to filter the report page with minimal space consumption.
Super Long Stacked Bar Chart
Second issue, when there are super small values next to large values, it is almost nearly impossible to click on the bar to enable the filtering. In the example image above, it’s easy to click on the value of 1,300 but almost impossible to click on the value of 10. Womp, Womp, and clicking on the bar text value does not enable filtering, insert ridiculous horn sound, or other familiar but annoying horn sound (as recommended by one of our readers Terence).
After some playing around with various visuals here is what I came up with. First, you must change the visual from a stacked bar chart to the matrix visual. On the Visualizations pane click on the Matrix visual.
Matrix Visual
This will change the visual to a matrix. It’s a little busy so we will clean it up a bit. On the Visualizations pane change the Matrix style to None, then open up the Subtotals section and set Row subtotals to Off. Your visual should now look similar to the following:
Change Matrix Style
Next we will add the “bars” to the visual. Open the Conditional formatting section and turn Data bars to On.
Turn on Data Bars
Short and sweet. Now we can properly resize the “text labels” of the y-axis and when we try to select small values such as 10, we are presented with a little grey selector bar, enabling us to select very small values.
Grey Selector Highlighting Bar
When you compare all three items side by side you can see that the most condensed version is the Matrix visual with conditional formatting bars. This provides you much more control when dealing with data that contains long text labels.
Comparison of Bar Charts
Note: There are many ways you can format your matrix to get the desired look. This tutorial is simply covering one type of look. Additionally, you could hide the text and grid completely by making the grid and column title colors of those match the color of your background, or use could choose one of the many of the grid type options to fit your style needs.
Thanks for following along, as always if you found this helpful please share it with someone who might find this helpful.
For those of you who are following my tutorials, you are most likely to understand that each tutorial comes with it’s own data source. This has been intentional so we can both walk through the example and learn by doing. Whenever, I am learning new things I find it’s helpful to do two things. First, make examples and demos of what I am trying to learn. Simply reading about a tool such as PowerBI, while engaging, it isn’t how I learn. I learn by getting my hands dirty, opening up PowerBI and creating something. You have to get utterly frustrated and stumped before that light bulb pops on and things become clear. Secondly, after I’ve taken the time to learn a new feature or method I find it extremely helpful to teach someone else what I’ve learned. By doing so, I am able share in the enthusiasm of others who also are learning. Teaching others also makes you remove all the cluttered ideas away from the essential lesson.
This being said, I consume a lot of data. Always finding data sources is difficult. Often I find myself making up my own data sets which takes additional time. At least, it used to take me forever to find data. This past week I was digging around for more data sources and I stumbled upon this gem. It is the data source has been used by many a bloggers for examples. It is Adventure Works sample business database. For this tutorial we will walk through how to load the Adventure Works dataset directly in to PowerBI.
Lets begin by opening Power BI desktop. Once Power BI desktop is open on the Home ribbon click the Get Data button. The Get Data window will open and under the Other section we find an OData Feed. Select the OData Feed and then click Connect to proceed.
Select OData Feed
The OData Feed window will open. Enter in the following HTML address into the URL field.
Note: In this example we are only using the basic OData Feed connection. Power BI does allow users to build more complex OData feeds which may require multiple parameters. This functionality is included by toggling the OData Feed screen from Basic to Advanced. This would be ideal if you had an OData Feed such as http://myOdataFeed.com/{custom parameter}/OData.
Click OK to proceed
Enter OData Feed
Power Bi will now go to the web address and read the OData Feed. What is returned is a list of tables. The Navigator window shows us previews of each table. Select the CompanySales and TerritorySalesDrillDown by checking the boxes next to each table. Click Load to proceed.
Select Tables
Ta Da!! that was easy. We were able to quickly load the two tables from the Adventure Works database. We can confirm this by looking at all the loaded fields contained in the Fields pane.
Loaded Fields
Add a couple of visuals with the following fields to pretty up our page.
Add Clustered Column Chart
Add Stacked Bar Chart
Pro Tip:notice how the two charts we added have the same color scheme but are different dimensions. The Clustered Column Chart has Years for the Colors, but the Stacked Bar Chart is using product categories. Change the colors on the Stacked Bar Chart by clicking on the Paint Roller on the Visualizations pane. Select the section called Data Colors and modify the colors for each product category.
Change Stacked Bar Chart Colors
Note: In order to sort from the largest sales amount to the smallest. Click the Ellipsis (small three dots) on the corner of the visual and select Sort by Sales. This will make the visual sort in order of sales from the highest amount to the smallest amount.
Similarly you can change the colors for the Clustered Column Chart to make the visual more appealing.
Change Clustered Column Chart Colors
Add Waterfall Chart
Again using the Ellipsis of the visual select Sort by Total to sort the number of sales by each employee from the largest sales amount, Linda to the smallest sales amount Lynn. Also, note that this chart is being built off the second table that we loaded from the OData Feed labeled TerritorySalesDrilldown.
Now, I believe we can say we are officially done. We have loaded data from our OData Feed and created three visuals upon that data. Thanks again for following along.
If you liked this tutorial please be sure to share. Also if you want to know more about me you can visit my About page and follow me on twitter or Linkedin for updates.
