Showing posts with label Power BI Desktop. Show all posts
Showing posts with label Power BI Desktop. Show all posts

Monday, December 25, 2023

Lesson 25 - Stacked Column chart in Power BI Desktop

A Stacked column chart in Power BI uses vertical columns to compare values across categories. Each column can be divided into multiple stacked segments, where each segment represents a subcategory and contributes to the total value of the column.

Stacked column charts are useful for comparing totals across categories while also showing the composition of each category.





Power BI Infographic

🚀 Learn Power BI in 5 Minutes

Skip the long read.
See the key Power BI concepts in one easy-to-follow infographic.

View Infographic →
How to proceed?


Step 1 Import the Data

Launch Power BI Desktop, create a new report page, and import the required data.

Refer Lesson 7 - Power BI Semantic Model to Build Great Visuals


Step 2 Add a Stacked Column Chart

From the Visualizations pane, select the Stacked column chart visual.


Step 3 Add Fields to the Stacked Column Chart

Add the required fields to the appropriate field wells in the Build visual pane:

X-axis – Country
Y-axis – Number of Participants
Legend – Gender

The X-axis displays the categories that you want to compare.

In this example, Country is added to the X-axis.

The Y-axis displays the numerical value for each category.

In this example, Number of Participants is added to the Y-axis.

The Legend divides each column into stacked segments based on a subcategory.

In this example, Gender is added to the Legend field to show the gender distribution within each country.

The Stacked column chart therefore compares the number of participants across countries while also showing the gender composition of each country.


Step 4 Apply Filters

Filters allow you to focus on specific data displayed in the Stacked column chart.

In this example, a Top N filter is applied to the Country field. The Bottom option is selected and the number is set to 6 to display the six countries with the lowest participant counts.

The By value field specifies the measure or numerical value used to rank the categories.

In this example, Number of Participants is used as the By value field to identify the bottom six countries based on participant count.

Step 5 Customize the Stacked Column Chart


You can customize the appearance of the Stacked column chart using the Format visual pane. Power BI provides formatting options under the Visual and General sections.

Select the Stacked column chart and use the available options in the Format visual pane to customize the visual.


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.

Colors

You can customize the colors of the stacked segments to distinguish the different subcategories displayed in the chart.

In this example, different colors can be used to distinguish the gender categories.

Configure the Layout

The available layout settings allow you to adjust the arrangement and spacing of the columns and series.

The Reverse order setting can be used to reverse the order of the series displayed in the stack.

Spacing settings can be used to control the space between categories and series, helping improve the readability of the chart.


Data Labels

Data labels display the numerical value of each segment within a stacked column.

You can customize the available settings such as the font, color, display units, and position of the data labels.

Data labels help users understand the values represented by the individual segments of each column.

Customize Total Labels

Total labels display the total value of all stacked segments in each column.

For example, if a column contains multiple gender segments, the total label displays the combined number of participants for that country.

Power BI supports both data labels for individual stack portions and total labels for the entire aggregated column.


In this example, the total labels are highlighted using red circles, while the data labels are highlighted using blue circles.




Step 6 
Save the visual

Finally, your Stacked column chart is ready. Save the Power BI report to preserve the visual and its formatting.




When to use Stacked column chart?


Stacked column charts are useful when you want to compare values across categories while also showing how different subcategories contribute to the total.

They can also be used to analyze changes in data composition over time when a time-based field is added to the X-axis.

Pros
  • Compares total values across different categories.
  • Shows how subcategories contribute to the total value.
  • Useful for analyzing composition across categories or time periods.
  • Supports data labels and total labels for additional value information.
  • Uses color-coded segments to distinguish subcategories.
Cons
  • Individual stacked segments can be difficult to compare because not all segments share the same baseline.
  • Too many subcategories can make the chart difficult to read.
  • A large number of categories can make the visual crowded.
  • Similar segment sizes can be difficult to compare accurately.


Conclusion


Stacked column charts are useful for comparing values across categories while also showing the composition of each category. Each column represents a category, and the stacked segments show how individual subcategories contribute to the total.

Carefully selecting categories, subcategories, colors, and labels helps create a clear and effective Stacked column chart.

Useful Information


Tags Power BI
Useful links Power BI - Stacked Column Chart Example - Power BI Docs
MS Learn Modules https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-column-chartsg
Test Your Knowledge Quiz

Saturday, December 02, 2023

Lesson 2 – Introduction to Power BI Desktop

If you're starting your data analytics journey, Power BI Desktop is one of the first tools you'll learn. It is Microsoft's free business intelligence application that allows you to connect to data, transform it, build semantic models, create interactive reports, and publish them to the Power BI Service for sharing and collaboration.

Whether you're a data analyst, business user, or student, Power BI Desktop provides everything you need to transform raw data into meaningful insights—all from a single application.

In this lesson, you'll learn what Power BI Desktop is, explore its main features, understand its different views, connect to various data sources, prepare and model data, create interactive reports, and publish them to the Power BI Service.

Power BI Infographic

🚀 Learn Power BI in 5 Minutes

Skip the long read.
See the key Power BI concepts in one easy-to-follow infographic.

View Infographic →

What is Power BI Desktop?

Power BI Desktop is a free Windows application developed by Microsoft for creating business intelligence reports and dashboards.

With Power BI Desktop, you can:

    • Connect to hundreds of data sources
    • Clean and transform data using Power Query
    • Create relationships between multiple tables
    • Build semantic models
    • Write DAX calculations and measures
    • Design interactive reports
    • Publish reports to Power BI Service
    • Collaborate securely with your team

Think of it as your complete report development environment before sharing reports with others. 

Why Use Power BI Desktop?

Organizations generate data from many different systems, including:

    • Excel spreadsheets
    • SQL Server
    • Oracle
    • MySQL
    • SharePoint
    • Azure SQL Database
    • Microsoft Fabric Lakehouse
    • OneLake
    • Dataverse
    • Salesforce
    • SAP
    • Google Analytics
    • Snowflake
    • Amazon Redshift
    • CSV and Text files
    • REST APIs
    • Hundreds of other connectors

Instead of manually combining this information, Power BI Desktop allows you to bring everything together in one place and analyze it visually.

Downloading Power BI Desktop

Power BI Desktop is available only for Windows.

There are two installation options:

Microsoft Store (Recommended)

  • Automatically receives monthly updates
  • Best option for most users

Microsoft Download Center

  • Manual installation
  • Useful for organizations that manage software updates centrally

Microsoft releases a new version almost every month, introducing:

  • New visuals
  • Performance improvements
  • AI enhancements
  • Fabric integration
  • Bug fixes
  • New connectors

Keeping Power BI Desktop updated ensures you have access to the latest features.

System Requirements

Power BI Desktop supports modern Windows operating systems.

Minimum recommendations:

  • Windows 10 or Windows 11 (64-bit)
  • At least 4 GB RAM (8 GB or more recommended)
  • Multi-core processor
  • Stable internet connection for publishing and cloud services

Power BI Desktop is not available natively for macOS.

Mac users can run it through:

  • Windows Virtual Machine
  • Parallels Desktop
  • Windows installed via Boot Camp (Intel Macs only)
  • Azure Virtual Desktop or other cloud virtual machines

Quick overview of the Power BI Desktop Tool



Power BI Desktop – Views

Power BI Desktop supports five different views, each designed to help you perform a specific task during report development.

  • Report – The default view used to create and design interactive reports by adding visuals, slicers, maps, tables, and other report elements.
  • Data – Displays the imported tables, rows, and columns, allowing you to inspect, validate, and understand your data before creating reports.
  • Model – Enables you to create, view, and manage relationships between tables, configure model properties, and organize your semantic model for efficient reporting.
  • DAX Query – Allows you to write, test, and analyze DAX queries to validate calculations, explore model data, and troubleshoot DAX expressions.
  • TMDL (Tabular Model Definition Language) – Allows you to view and edit your semantic model using the Tabular Model Definition Language. This view is primarily intended for advanced users and developers who manage semantic models using a text-based format and source control.

Connect to different data sources

Power BI Desktop enables you to connect to a wide variety of data sources, whether they are stored on premises, in the cloud, SaaS applications, databases, files, or Microsoft Fabric. It supports hundreds of built-in connectors, allowing you to combine data from multiple sources into a single report.

The screenshot below shows the Get Data experience in Power BI Desktop, where data sources are organized into categories such as All, File, Database, Power Platform, Azure, Online Services, Fabric, and Other. We will explore how to connect to these data sources in detail in the upcoming lessons.

To connect to a data source in Power BI Desktop, click Home > Get Data, or select Get Data > More... to browse all available connectors.










Cleanse and Transform data

Data is rarely perfect in real-world scenarios and often requires cleaning and transformation before it can be analyzed. Power BI Desktop includes the built-in Power Query Editor, which enables you to prepare and shape your data without writing complex code.

Using Power Query Editor, you can perform a wide range of data preparation tasks, including changing data types, filtering rows, removing or renaming columns, replacing values, splitting or merging columns, merging or appending queries, combining files, handling missing values, and performing many other data transformation operations.

To open Power Query Editor in Power BI Desktop, click Home > Transform data.





Power Query Editor provides “Applied Steps” feature that allows to see the history of all transformations made to the dataset.







Create Data Model

Power BI Desktop enables users to combine data from multiple sources and build a semantic model, which serves as the foundation for reporting and analytics. A well-designed semantic model improves report performance, simplifies data analysis, and enables accurate calculations.

Using the Model view, you can create, view, and manage relationships between tables, configure model properties, and organize your data model. If relationships are not already defined in the underlying data sources, Power BI allows you to create them manually.



Create Reports

Once your data has been prepared and your semantic model is ready, you can create interactive reports using Power BI Desktop. A Power BI report consists of one or more pages containing a collection of visuals that help you analyze and present your data effectively.

Power BI provides a wide variety of built-in visuals, including bar charts, line charts, pie charts, tables, matrices, maps, KPI cards, gauges, slicers, and many more. You can also enhance your reports using custom visuals available from Microsoft AppSource.

Reports can include multiple pages, interactive filters, drill-through functionality, bookmarks, buttons, tooltips, and other features that enable users to explore data and gain meaningful insights.


Share Reports

Reports created using Power BI Desktop can be shared with other users after they are published to the Power BI Service. Once published, authorized users can securely access and interact with the reports using a web browser or the Power BI Mobile app on their Android or iOS devices.

Depending on their permissions and licensing, users can also collaborate on reports, share dashboards, subscribe to reports, and access them from anywhere using the Power BI Service.




Conclusion

Power BI Desktop is a powerful and easy-to-use tool for creating interactive reports and dashboards. It helps you connect to different data sources, prepare your data, build semantic models, and create meaningful visualizations—all in one place.

In the next lesson, we'll explore the Power BI Service and learn how to publish, share, and collaborate on reports.

Useful Information


Monday, May 15, 2017

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/

Sunday, December 18, 2016

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.

Tuesday, November 01, 2016

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.
PowerBI-Preview
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: ,

Sunday, October 23, 2016

Google BigQuery – Analytics Data Warehouse

GoogleCloudPlatformGoogle 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.

Power BI Desktop – Google Analytics Integration

PowerBIDesktop
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”.
PowerBI-GA-1
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”
PowerBI-GA-2
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.
PowerBI-GA-2a
PowerBI-GA-2b
PowerBI-GA-2bb
PowerBI-GA-2c
e) Once provided the credentials and access to Power BI you can then connect to your Google Analytics account from Power BI Desktop.’
PowerBI-GA-2d
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.
PowerBI-GA-3
PowerBI-GA-4
g) Now you can start building your visualisations on your Google Analytics data using Power BI Desktop similar to the below one.
FinalOutput