Thanks for all attendees of my webinar yesterday in PASS Business Analytics Virtual Chapter (Group). It was a pleasure to present to you all again. The session time spent all for me speaking and there was no time for answering questions at the end, so here I’ll answer all questions, and you will have access to materials of the session plus access to the session recording. Special Thanks to Dan and Paras for the opportunity for me to speak, and everyone who gave me their valuable feedback to make this session better moving forward.
Session Title: Take Power BI Visualization to the Next Level
Visualization is the front end of every BI system, and getting it right will affect tremendously on the data analysis solution. In this session you will learn tips and tricks of data visualization with Power BI, examples of effective visualizations, and you will learn when is best to use which type of chart. You will learn about best way of visualizing elements through built-in visualizations or custom visuals.
Agenda
- Importance of Visualization
- Choosing the Right Visual/Chart
- Line Chart
- Bar/Column Chart
- Scatter
- Table/Matrix
- Combo Chart
- Interactivity
- Grouping and Binning
- Colors
Video Recording
If you are interested to watch the video recording of the session, here it is:
Questions and Answers
Q: In a stacked column chart with year on the x-axis and sales by product sub-category on the y-axis, is it possible to show the percentage of each product subcategory’s sales as compared to the total sales for that year without coding in DAX?
A: Not sure if I understand your question correctly. In column chart you can have only a measure on y-axis (so you cannot have sub-category there), You can have only one categorization in column chart which will be your X-Axis. you can then use a Legend to drill down into sub categories. So in your case that you are using three categories (year, product category, and product sub category) column or bar chart even stacked or clustered won’t be able to show it. with Stacked/Clustered column/bar chart you can show up to two categories with one or more measures. and yes, you can use Quick Calc in that scenario to get he percentage of the grand total (not specific category). if you want percentage of subcategories on a specific category, then using 100% Stacked column chart would be better option. Read this article for more information please.
Q: In Power BI, using the mapping feature, is there a way to plot more than one dimension on the map? For instance, if you want to plot both gas stations as points on a map and ATM locations as a different icon points on the map, is that possible?
A: Yes, it is. I recommend using ArcGIS map visual for Power BI. This is not a custom visual, however it is preview features. for using that you need to enable preview feature in Power BI options. After enabling that you will also need to restart your Power BI Desktop. With this map you can have different shapes one for each measure (or set of location points). or you can use different layers on the map, like having one measure as heat map and another as bubbles. there is also one great feature in ArcGIS map which is adding layers of pre-defined maps of ESRI. There is a very big database of many maps in ESRI ArcGIS, like map of gas stations of a country, pipelines, school zones and many other types of maps which you can add as separate layers to your map visual. I’ll write about this soon in a blog post.
Q: is it possible to manage groups dynamically with a table that has the groups saved so you don’t have to group the bars manually?
A: Of course it is. You can have groups and categories defined in your data tables (in the data source; SQL Server, Excel, Oracle or whatever it is). In that case you won’t need to use grouping and binning feature in Power BI Desktop at all. having grouping in the data source would make it more manageable as well as you mentioned. So I would recommend doing it in this way, however this feature exists in Power BI Desktop mainly for Business Analysis to do it without a need to change the data source. Proper modelling normally will have these as part of the model. If there be a time in the feature I’ll do another session on data modeling tips and tricks.
Q: If you lock the total sales amount and have other window show filtered amount can you set up a percentage of total off those two? (this was with regards to the Power BI interactions and having the two card visualizations)
A: I would recommend doing that through DAX, it is much more reliable, and you don’t need to be worry about the interaction of visuals then. You can create a DAX expression with SUMX or CALCULATE function to calculate the grand total of a measure, and another SUM for just summarizing of the selected filter context, then DIVIDE of these two will give you the percentage.
Q: Can you show the data model again?
A: All examples cam from AdventureWorksDW model. You can download the Excel version of it from the link below this post.
Q: in the filters section, can you write in your own code in powerBI like you can in tableau?
A: You can write your own code in Power BI too, but not in the filters section; in Data Tab with Measures, Calculated Columns, and Calculated Tables (which can be used for filtering simply, here is an example article). You can also use Power Query’s Query Editor for doing that. There are many places that you can write your own code, and you have full flexibility on that, then you can use it simply in the filtering section. For example; if you want to create a filter on everything that Age is less than 1 year ago, you can create a calculated column (in DAX or Power Query), and then in Filter section put a advanced filter on everything that Age (in years) is less than or equal 1. I have done heaps of Power BI projects so far, and haven’t found a filter that i cannot apply.
Q: How can you import custom color palettes into Power BI instead of manually having to change the color pallete?
A: Color Palettes are not yet available in Power BI unfortunately. However Power BI team mentioned that they are working on it, so it should be available sometime in the feature 🙂
Download
Presentation Slide deck and Demo file is available here for you to download;
Save
Save
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.