Exploring Power BI’s Analytical Prowess: Unleash Your Data’s Secrets
Microsoft’s Power BI (Business Intelligence) has great potential to creating dynamic and sophisticated reports. By following these tips and tricks, you can construct a report in just one day with dedicated exploration, a process that took me 2–3 weeks while sifting through a various learning tutorial.
This can be a very informative article please do it at your own pace & suitable for any interview process.
P.S. Power BI is only available to Windows OS users.
Pre-requisites:
- A grasp of fundamental statistics concepts and familiarity with various types of charts and visualizations.
- Data Manipulation, Basic Database Concepts, Problem-Solving Skills, Domain Knowledge and Practice and Exploration.
Example Dashboard:
Title: Connect to a Data Source / Change the Data Source
For this article, I connect the csv file directly by choosing ‘Text/CSV’ option.
But on numerous occasions, I have used Sharepoint Folder, Sharepoint List or Salesforce Object as well.
Utilizing these choices enables you to retain access to your real-time modified data. Otherwise, you might need to re-establish the connection to your data source, resulting in the need to reconstruct your charts.
Therefore, if you are confident that you won’t be altering your data by introducing new columns or modifying titles, working with MS Excel is a viable option.
Of utmost significance, ensure to click on “Transform Data” and validate it prior to loading your data into Power BI.
Title: Data Analysis — Determining Appropriate Chart Type for Your Needs
Understanding what type of chart will allow you to make your analysis is the most challenging part of the Data Analysis journey.
The key is to think like a leader or your manager.
In my Employee Analysis data, I would like to first see the “Age v/s Performance Score”. This dashboard has changed font type, color & font size formats will allows me to view the Charts as per my company pre-defined standards.
The charts in this dashboard are designed to provide users with insights into the performance scores received by people in different age groups, including the minimum, average, and maximum scores. The last chart aims to show the performance ratings of employees in the company and the percentage of total individuals in each rating group.
Title: Report, Data and Model View
This is a particularly intriguing view as it allows someone to modify the way the data within the columns appear.
The report view is the Dashboard or the Report an individual creates to represent information in a pictorial or easy to understand format.
The table view will allow you to modify your data in a very easier way and applies the changes immediately to your reports.
The model view illustrates the relationships that each table has with other tables, similar to the relationships existing in a database. These relationships include one-to-one, many-to-one, one-to-many, and many-to-many connections, which establish the links between tables for efficient data retrieval and report generation with values from different tables. You can modify these relationships as per your analysis.
Title: Different Charts
You can create different charts that can be imported for representing different data types. For instance, stocks are better viewed on daily basis by using Candlestick charts imported from ‘Power BI Visuals’ marketplace.
Another example of a different chart is “Clustered Column Chart” wherein I am showcasing different Job Levels held by Females and Males in this company.
Title: Formats and Visuals along with Company standards
Company standards encompass guidelines for fonts, font sizes, and colors that highlight crucial aspects of the company’s identity. Employing a consistent color palette enables employees to generate uniform reports, promoting a sense of integrity across the organization visible to clients.
Always check with your supervisors for these vital details!
Title: Dashboards & KPIs
From a company’s perspective, the distinction between a dashboard and a report lies in their presentation and interactivity. Dashboards are overview reports that focus on specific KPIs (Key Performance Indicators) monitored within a project. They provide visualizations without interactive features like “slicers” or “filters”.
On the other hand, reports are dynamic charts that allow users to manipulate data, enabling a deeper understanding of the information presented.
Title: Themes
These are easy to use color palettes that make the selection of what combinations to use together for making the chart look more professional.
“Go to View” and then click on “Themes” to checkout various combinations that make the charts looks more sophisticated and aligned.
Look at the difference it made immediately to my dashboard! I have the perfect shades of Blue, Green and Grey that makes it look neat, systematic and readable.
Title: Using Filters for Your Power BI Reports
They are your saviors when you don’t want certain data to be not included in your reports. I have highlighted filters in yellow in the image below.
For instance, this report represents the data for female employees.
The applied filters focus on female employees across various job levels, providing insights into their environment satisfaction and job involvement levels. Additionally, the data allows us to identify the representation of female leaders within the company.
Title: Slicers & Connecting them to different Charts
Diverse slicers simplify the portrayal of data specific to a particular chart. For instance, you can observe the Median Hourly Rate (in Dollars) based on Gender. This eliminates the necessity of manually filtering Gender data, as these filters seamlessly consolidate essential details within a single report.
I’ve employed distinct colors to emphasize various slicer types. I advise against utilizing such colors in your reports unless they convey a formal appearance.
Various slicer options, including single select, multi-select, and various types like dropdowns, vertical lists, and tiles, contribute to the dynamic nature of reports. Such functionalities are not readily available in Python libraries or Jupyter notebooks. Fostering creativity while crafting easily comprehensible reports is crucial in this context.
Title: Wallpaper & Canvas Settings
Enlarge the dimensions of your wallpaper by accessing “canvas settings” as depicted in the example below:
You can simply click outside your charts, and you’ll be directed to “Canvas Settings,” accessible within the “Format Your Report Page” menu. To modify background color of this report, navigate to the “Wallpaper” option.
Title: Percentage calculations and showing them in Gauge chart
Calculating percentage is easy if you know the math behind it.
For instance,
Male Training Time Percentage = (Total Male Training Time / Total Training Time) * 100
Female Training Time Percentage = (Total Female Training Time / Total Training Time) * 100
After this calculation, we need DAX formulas,
Male Training Time = CALCULATE(SUM(YourTableName[TrainingTime]),YourTableName[Gender] = “Male”)
Total Training Time = SUM(YourTableName[TrainingTime])
Male Training Time Percentage = DIVIDE([Male Training Time], [Total Training Time]) * 100
This can now be added to Power BI Report wherein I prefer a Gauge Chart that shows Achieved and Target percentage.
Title: Fiscal Year grouping
At some point, you might encounter the need to organize your data by your company’s Fiscal Year. Although this process might seem extensive, it’s actually quite straightforward.
Navigate to the “Transform Data” section and choose the desired table where you intend to introduce the new column. Once there, click on “Add Column” and choose “Conditional Column”.
You’ll notice the conditions I’ve implemented for the “StartDate” of each employee, ensuring that the dates align with the targeted FY group.
Condition 1: The StartDate should precede April 1, 2023, designating it as part of the Previous Fiscal Year.
Condition 2: The StartDate should be on or after April 1, 2023, marking it as part of the Current Fiscal Year.
This inventive approach sets itself apart, offering a distinctive solution that you won’t encounter elsewhere. I have saved the best for the end.
Furthermore, you have the flexibility to adapt this technique and generate distinct columns for FY22, FY23, and subsequent fiscal years. You can use grouping, filtering, slicing options on this technique. This approach greatly simplifies calculations, particularly when extracting percentages. This is how I have used in my projects and got a shoutout for excellent problem-solving skills.
Should any doubts or queries arise, please don’t hesitate to reach out to me via my webpage . I’m more than willing to provide assistance and support.
The tutorial comes to an end with this information. This compilation equips you comprehensively for an interview focused on Power BI. Skip the textual content and dive into hands-on practice, as theoretical queries about constructing visual reports are less likely to be asked in an interview. Instead, interviewers will inquire about the complex tasks you tackled to enhance the analytical potential of your Power BI reports.
These are just enough to make you stand out of the crowd!
Have fun learning!