Lesson 25 - Stacked Column chart in Power BI Desktop

 A stacked column chart is a visual representation that uses vertical columns to display data. Each column represents a category or group, and it is divided into segments to depict subcategories or individual components of the data. It helps illustrate the composition of a whole while showing the contribution of individual parts.

How to proceed?

Step 1

Launch power BI desktop app and open the new report page and import the data required. 

Refer Lesson 7 - Power BI Datasets to build great visuals

Step 2

In “visualizations” pane click on “Stacked column chart” which is highlighted in the given figure.

Step 3

Drag the data fields into “Field Section “that you want to analyze.  
using the stacked column chart.

X-axis: Represent categories or groups that you want to compare - Country

Y-axis: Represents numerical value that you want to display within each category or group in X-axis – Number of Participants

Legend: Represents sub-categories within each group displayed on X-axis. Subcategories are visually stacked within each column - Gender.

Step 4

Filters in a stacked column chart enable users to selectively focus on specific categories or subcategories of data for deeper analysis.

Here, I utilized a "Top N" filter, specifying "Bottom" as the selection in "Show items" and set the number to 6 to display the six countries with the fewest participants.

The "By value" option enables us to filter data based on specific conditions. In this case, I used it to filter out the bottom 6 countries with low participant counts.

Step 5

Customizing the appearance

You can customize the appearance of the visual.  You can change Title, Font size, Style, Colors and Data labels. Click anywhere on the visual and set the below properties in the Format section.

Refer Lesson 6 - Formatting the visuals in Power BI Desktop

You are allowed to choose colors for sub-categories to differentiate them. This improves clarity and helps viewers easily identify and understand the distinctions between various sub-categories within your chart.

“Reverse stacked order” option allows you to rearrange the order of data series easily. 

Spacing refers to the gap or distance between individual columns or bars within the chart. Adjusting the spacing can impact the overall appearance and readability of the chart.

Data labels are used to display specific numerical values associated with each individual stack within each column. You can adjust the font, color and position of the data labels within the chart.

Total labels represent the sum or total value of all the individual segments in a given column. This label shows combined value of all the sub-categories within each category.

In this figure, I have highlighted total labels using red-colored circles and data labels with blue-colored circles.

Step 6

Save the visual

Finally, your Stacked column chart is ready. Click save button to save the visual.

When to use Stacked column chart?

Stacked column charts are used when you want to visually represent the composition of data points or categories by stacking segments to show how they contribute to the whole. You can use the stacked column chart for following scenarios.

• When you want to analyze and contrast various categories or subcategories within a dataset.
• When you want to track changes on data composition over a period of time, you can use stacked column chart.
• To enhance the ability to spot patterns, anomalies, or trends in data composition more effectively, utilize the color-coded segments in a stacked column chart.

Pros

• Stacked column charts are easy to make and understand, so wide audience can use them. 
• Effectively displays trends over a period, especially when a time series is placed on the X-axis.
• The automatic aggregation of values within each category simplifies the presentation of cumulative data.

Cons

• Stacking can lead to loss of precision in showing each separate piece of data because the values are aggregated.
• Using excessive subcategories in a stacked column chart can result in confusion and misunderstanding.
• When dealing with a large number of categories over time, the chart can appear crowded and become challenging to comprehend.

Conclusion

Stacked column charts are a useful visualization tool for displaying proportions, tracking trends over time and highlighting relative compositions within datasets. Whether you are analyzing budget breakdowns, sales distribution, or market segment compositions, a stacked column chart often serves as the most effective visualization tool.

Tags	Power BI
Useful links	Power BI - Stacked Column Chart Example - Power BI Docs
MS Learn Modules	Visualization types in Power BI - Power BI | Microsoft Learn
Test Your Knowledge	Quiz

Power BI Premium

As part of the Data Amp event hosted by Microsoft this month, they made an announcement about Power BI Premium. Currently with SQL Server 2016 Reporting Services, you can upload Power BI Desktop reports to the SSRS Web Portal (Power BI Desktop needs to be installed on consumer devices)

With the January 2017 preview release of SSRS, Microsoft announced the ability to upload Power BI Desktop reports to the SSRS Web Portal along with serving them within the web browser without the need for Power BI Desktop installed on consumer devices.

Power BI Premium introduces the ability to manage Power BI reports on-premises with the included Power BI Report Server. Power BI Report Server includes all the features of SQL Server Reporting Services including the ability to publish and serve Power BI Desktop reports. Power BI Report Server will be generally available late in the second quarter of 2017.

So with the release of Power BI Report Server, SSRS in SQL Server 2017 will support only Paginated and Mobile Reports while Power BI Report Server will support Paginated, Mobile and Power BI Desktop reports. Good news is Power BI Report Server also supports Custom Visuals.

With Power BI Premium, if you want to distribute and embed Power BI Reports in your applications you no longer require to purchase per-user licenses.

Power BI Premium Pricing - https://powerbi.microsoft.com/en-us/pricing/

Power BI Premium Whitepaper - https://aka.ms/pbipremiumwhitepaper

Power BI Premium Calculator - https://powerbi.microsoft.com/en-us/calculator/

Access Denied error when trying to import Power BI Desktop report using Power BI Embedded – ProvisionSample

When you try to import the Power BI Desktop report using the ProvisionSample (https://github.com/Azure-Samples/power-bi-embedded-integrate-report-into-web-app/) you might be asked for File Path and make sure to include the complete path including .pbix file. e.g. c:\temp\Test.pbix and also make sure the .pbix is closed before doing the import.

Technorati Tags: Power BI Desktop,Power BI Embedded

How to choose the right reporting tool?

Download PDF

Technorati Tags: SSRS2016,Mobile Report,Power BI Desktop,Paginated Report

Power BI Preview Features

If you would like to keep track and try the Power BI Preview features, you can view them under Power BI Desktop > Options > Preview Features as shown below.

Note: Preview features might change or removed in future releases so need to be cautious on how to use them.
Thanks Christopher Webb and Marc Reguera for sharing this tip.

Technorati Tags: Power BI Desktop,Power BI

Google BigQuery – Analytics Data Warehouse

Google handles Big Data every second of every day to provide services like Search, YouTube, Gmail and Google Docs. Google created a Query Service named “Dremel” which was used just internally within Google.
Dremel is a query service that allows you to run SQL-like queries against very, very large data sets and get accurate results in mere seconds. You just need a basic knowledge of SQL to query extremely large datasets in an ad hoc manner.
BigQuery is the public implementation of Dremel. BigQuery provides the core set of features available in Dremel to third party developers. It does so via a REST API, a command line interface, a Web UI, access control and more, while maintaining the unprecedented query performance of Dremel.
BigQuery can scan billions of rows in a highly performant manner for ad hoc query analysis. It does achieve high performance through Columnar Storage and Tree Architecture. BigQuery Client Libraries - https://cloud.google.com/bigquery/client-libraries
Currently Microsoft is planning to provide Google BigQuery connector for Power BI. In the interim, you can import data from Google BigQuery using an ODBC driver, which is fully supported for Import scenarios in Power BI Desktop, and Personal/Enterprise Gateway for Refresh purposes.

BigQuery vs MapReduce

MapReduce is a distributed computing technology that allows to implement custom “mapper” and “reducer” functions programmatically and run batch processes with them on hundreds or thousands of servers concurrently. MapReduce is designed as a batch processing framework, so it’s not suitable for ad hoc and trial-and-error data analysis.
BigQuery is designed to handle structured data using SQL.MapReduce is a better choice when you want to process unstructured data programmatically. The mappers and reducers can take any kind of data and apply complex logic to it.
Use BigQuery

• Finding particular records with specified conditions. For example, to find request logs with specified account ID.
• Quick aggregation of statistics with dynamically-changing conditions. For example, getting a summary of request traffic volume from the previous night for a web application and draw a graph from it.
• Trial-and-error data analysis. For example, identifying the cause of trouble and aggregating values by various conditions, including by hour, day and etc...

Use MapReduce

• Executing a complex data mining on Big Data which requires multiple iterations and paths of data processing with programmed algorithms.
• Executing large join operations across huge datasets.
• Exporting large amount of data after processing.

Technorati Tags: Google,BigQuery,MapReduce,Power BI Desktop,Power BI

Power BI Desktop – Google Analytics Integration

Download Power BI Desktop or from the Power BI Portal. Power BI provides out of the box integration with Google Analytics through the Google Analytics Core Reporting API. Google Analytics Core Reporting API change log to track any changes released by Google.
I tried to get some analytics on my blog through Power BI Desktop – Google Analytics Integration.
Note: The Google Analytics content pack and the connector in Power BI Desktop rely on the Google Analytics Core Reporting API. As such, features and availability may vary over time.
a) Open Power BI Desktop – Free tool provided by Microsoft to install on desktops and immediately start pulling data from disparate sources and start building Visualisations over that.
b) Click on “Get Data” from the tool bar and select “Online Services” and choose “Google Analytics”.

c) You will then displayed with the message advising that Power BI connects to a third party service which in this case is the Google Analytics Core Reporting API. You can check the Don’t warn me again for this connector checkbox and click “Continue”

d) If you haven’t already connected your Google Analytics account with Power BI Desktop then you will be provided with the below screen to connect to your Google Analytics account.




e) Once provided the credentials and access to Power BI you can then connect to your Google Analytics account from Power BI Desktop.’

f) Select the Google Analytics account and start choosing your dimensions and measures you would like to analyse and Power BI Desktop will import them for you.


g) Now you can start building your visualisations on your Google Analytics data using Power BI Desktop similar to the below one.

Technorati Tags: Power BI,Google Analytics,Google Analytics Connector,Power BI Desktop