We continue the series of materials on BI-systems and today we will have a look at the dashboard prepared in PowerBI using the SuperStore Sales dataset. We will cover how to connect the data to the system, set custom colors for visualizations and create new measures, implement switching between charts using bookmarks and we will discuss the challenges that we faced when building the dashboard.

This is the how the final dashboard looks like:

The most notable feature of the dashboard is data cards that show the company’s KPI. The cards compare the parameters to the same period in the previous year and show the previous year’s dynamics in the background.

Below we can see the chart that shows top-performing provinces. The bluer the rectangle the more profitable the province, the more orange the rectangle the more losses the province sustains. The size of the rectangle corresponds to the quantity of sales. We can click on rectangles to see more detailed information about profits and sales dynamics in the region on the graph on the left and their KPI at the top. On the graph, there are green and blue points that indicate the month of the current year and the previous year respectively. Hovering over these points, you can see a trend line.

The next part of the dashboard shows product and customer analysis. This part allows us to answer questions such as “which products were the most profitable or unprofitable” or “which customers contributed to most of the profits or most of the losses”.

Data collection

To connect the data we used an Excel file. PowerBI offers a number of sources to connect your data from such as Excel, csv, json files and various databases.

Configuring reports and visualizations

When building a dashboard in PowerBI we wanted to copy the color themes from Tableau. To do this, we have created a JSON file with the list of colors that we want to use. You can see the content of our file below. Then in the views tab, we clicked on the “browse for themes” button and uploaded our colors.

{
	"name":"Orange-Blue Diverging",
	"dataColors": [
		
		"#1c5998",
		"#1c73b1",
		"#3a87b7",
		"#67add4",
		"#7bc8e2",
		"#cacaca",
		"#fdab67",
		"#fd8938",
		"#f06511",
		"#d74401",
		"#a33202",
		"#7b3014",
		"#F07C28",
		"#2B5C8A",
		"#94C6E1",
		"#87d180",
	]
}

Then we have created a separate table called Calendar and populated it with all order dates. After that, we created a column with just a month and a year to create a filter based on it.

Creating necessary measures

When creating a dashboard with PowerBI we often need to create new measures. For the data cards, we created such measures as Total Profit, Total Sales, Total Orders, Total Clients and so on. The arrows that you can see in the data cards are also customized and a measure was created for each of them. To apply the color to arrows we formatted the color by rules and indicated red if the value is less than 0, green if the color is more than 0.

Adding bookmarks to switch between charts

To switch between charts, we added bookmarks for sales and profits. For the sales chart, the profits bookmark is hidden and vice versa. The button was downloaded from the internet and added to the respective bookmarks.

Interesting features and challenges we faced when building the dashboard

We have created custom data cards for KPI which are different from the default ones offered by PowerBI. The original features of cards include the background trend, the name and value while the arrows and changes are a custom feature. Another interesting feature that we used is cross filtration which allowed us to apply the filter to both the profits/sales chart and KPI cards.

One of the challenges that we have faced was the inability to build a bar chart with 2 categories. This feature was not implemented in PowerBI at the moment of writing this overview (maybe it is implemented now), so we had to create a table and add bar charts into it. Similarly, we inserted bar charts into the Top Customers table.

Conclusion

Our team has evaluated the dashboard and has given the following scores from 1-10 scale (10 being the highest) to this dashboard:

1. Meets the tasks – 9.8
2. Learning curve  – 3.0
3. Tool functionality – 9.5
4. Ease of use – 7.5
5. Compliance with the layout – 9.5
6. Visual evaluation – 8.8

Overall: 8.0 out of 10. Have a look at the final dashboard here.

This year I succeeded in becoming a Tableau Desktop Certified Associate. When I was thinking about how to prepare for the exam, I came across e-learning courses from Tableau that turned out to be free for 90 days.

I decided not to waste such an opportunity and complete all the 3 modules in Fundamentals at a fast pace. When I got certified, I was wondering which programs are offered by other producers of BI tools. First things first, I decided to study training materials on PowerBI. In this small article, I would like to compare Tableau and PowerBI training programs.

Disclaimer: in the end, I have formed an unfairly prejudiced and positive attitude towards Tableau, so PowerBI supporters may not like this article and find it biased (in all fairness, there are also words of praise for PowerBI).

After having studied the training materials, I can finally state the reasons why I am definitely in favor of Tableau as a tool for data analysis and visualization.

First of all, there is a huge gap in the approach to materials and the assessment of their understanding. Although Tableau training materials are more technical and pay less attention to design, by studying through their videos you can do excellent visualization. After completing all three steps of Tableau training, a strong desire to create new stunning reports with the use of LOD Expressions, Filter Actions, and make convenient interfaces arises. However, after watching all the materials on Power BI the only question that remains is why did I waste my time?

Emotions aside, there are several key points that turned out to be important after having studied the material.

This is a good dashboard according to Microsoft

The quality of content and training examples

If you consider the way training videos are presented in Tableau and the questions in a quiz format that are posed at the end of the covered material, you start understanding the idea of the software. But in the case of Power BI, you will be totally disappointed. Have a look for instance at the material for identifying outliers, here Microsoft suggests building a scatter plot and visually identifying all the outliers.

Design of reports and dashboards

There is some objective criticism towards Tableau training materials on the topic of graph design and control elements, but they are still neatly and beautifully made. Now have a look at the dreadful thing that Microsoft suggests as the result of the analyst’s work. And this is a well-built dashboard according to Microsoft.

Assessment of the knowledge gained during the training

During the training at Tableau, immediately after a small lecture, you learn by applying the part of the studied material in practice. You need to click certain buttons in the interface to solve a problem. Power BI offers “labs” that are supposed to be launched from a remote machine. I didn’t manage to start a single lab; I wrote to the support 3 times and the support couldn’t solve my problem so I didn’t manage to experiment over the PowerBI tasks.

The results of the analyst’s work according to Microsoft.

Other points are mostly related to the software rather than the training program.

Cross-platform support

I have been working with Tableau for a long time and 4 years ago I switched to Mac. After the transition from Windows, my experience of using Tableau did not change. In fact, Tableau was developing and I was developing with it, but the team did not change the key elements of the interface. I have been experimenting with building reports in PowerBI, but I was uncomfortable with different Microsoft archaisms like publications through some share-portal where you need to have an MS account and configure something through the administrator. All of this was a terrible headache.

However, what struck me so much was that I could not use PowerBI on a Mac. There is absolutely no way and this is a principled stance of Microsoft which is not expected to change in the future. From my point of view, such software belongs to a B2B segment in the field of analytics, assumes the connection to all kinds of DBMS, but denies the existence of an alternative operating system which could be used by a number of potential consultants that could use and promote PowerBI as an analytical tool.

Most certainly, there are some rational reasons why any software from Microsoft doesn’t work very well on Mac, but the simple truth is that for me the software remains inaccessible. Nevertheless, I wasn’t looking for an easy way out and installed PowerBI through Parallels in order to honestly consider the tools again taking into account the training materials.

Visualization options

Both Tableau and PowerBI offer stunning visualization options. In fact, in this regard, PowerBI offers a video with a little more information than usual. So, on this matter, the tools are presented equally well.

Functionality

Here I want to give credits to the functionality of PowerBI. In fact, the variety of tools is extremely wide even without connecting third party libraries. For example, automatic clustering, decomposition tree, data profiler and setting filters on a graph.

Internal language syntax

To work with PowerBI you need to learn DAX. It is not a programming language, but a functional language. You won’t be able to write your own code, however, you won’t even need it – all the functions are already implemented, so you should only learn how to use them. Microsoft tells about DAX quite well in the manual. Definition of a new measure in DAX looks like this:

Revenue YoY % =
DIVIDE(
	[Revenue]
		- CALCULATE(
			[Revenue],
			SAMEPERIODLASTYEAR('Date'[Date])
	),
	CALCULATE(
		[Revenue],
		SAMEPERIODLASTYEAR('Date'[Date])
	)
)

Preparing data for the analysis

Inside PowerBI there is a Unpivot feature that allows bringing the data in columns with time periods into the form that is convenient to use in pivot tables.

However, an ETL tool for data cleaning and wrangling in Tableau Prep also has this feature implemented.

Conclusions:

1) The training programs are built in completely different ways, the methodology of immersion into Tableau tools is more elaborate and efficient. There is an opportunity to get practical experience of solving problems and get feedback (albeit automatic).
2) Reports and dashboards design in training materials from Microsoft hardly look professional while Tableau’s implementation is much better.
3) Knowledge assessment at Microsoft is implemented at the abysmal level (absolutely perfunctory tests like in a bad school) while at Tableau it’s much better implemented, you dive into the problem, think about the answer and solve it.
4) Cross-platform support is not PowerBI’s strongest point, however in the case of Tableau it’s an excellent competitive advantage.
5) The functionality and capabilities of the tools are certainly at the highest level, and in some points, PowerBI wins.

Have a look at our dashboard reviews in Tableau and other BI tools.

In our new series, we will try to give a detailed representation of  several BI tools using the SuperStore Sales dataset. The data in SuperStore Sales reflect sales and profit of the retail chain in US dollars.

In the upcoming blog post, we will discuss a real problem statement that could arise when creating a dashboard based on the SuperStore Sales data and design a functional layout to provide clear answers. Throughout this task, we’ll stick with a predefined set of colors to make the comparison more unbiased.

Next, we’re going to create a dashboard that would assist in data-based decision-making with each of the BI tools. We also plan to involve industry experts to learn from their experience.

A complete list of BI systems and tools to be tested in our experiment is provided below. I want to welcome everyone who is willing to help us in solving this challenge to message me on Telegram  – @valiotti. I will be glad to hear from you. Although it’s a non-profit project, it’ll be really useful for the open-source community.

We plan to cover the following list of tools:

Free Open Source:

• Metabase
• Redash
• Apache Superset
• Dash / Plotly

Free Cloud-Based:

• Google Studio
• Yandex Datalens

Paid Cloud-Based:

• Mode
• Cluvio
• Holistic
• Chartio
• Periscope
• DeltaDNA
• Klipfolio
• Count.co

Paid:

• PowerBI
• Tableau
• Looker
• Excel
• Alteryx
• Qlik Sense
• Qlik View

The final goal is to evaluate the BI tools against the following criteria:

• learning curve of BI tool (1 — too hard to learn, 10 — easy)
• tool functionality (1 — very poor functionality, 10 — multifunctional)
• ease of use (1 — very inconvenient, 10 — super convenient)
• compliance of the result (1 — far from the designed layout, 10 — too close to the designed layout and objective)
• visual evaluation (1 — poor appearance, 10 — great visual appearance)

An integral weighted score for each tool will be calculated based on the internal estimates.

The results will be posted to our Telegram channel @leftjoin_en and followers will also be able to share their thoughts on the experiment.
By the end, each tool will be represented as a point in the plane, which will be divided into 4 parts.

This article will be updated with links and ratings as we new posts come out.