The text is a series of excerpts describing the process of creating data visualizations and dashboards using Power BI. It guides users on how to import, transform, and model data from sources like Excel using Power Query. The text covers topics such as cleaning data, creating relationships between tables, and using DAX functions to perform calculations. Various chart types are explored, along with techniques like drill-downs, conditional formatting, and grouping data using bins and lists. The final portion focuses on building a comprehensive dashboard from survey data, including considerations for layout and theme customization.

Power BI Mastery: A Comprehensive Study Guide

Quiz: Short Answer Questions

What is Power BI and what is its primary function? Power BI is a data visualization tool within the Microsoft ecosystem used for creating interactive dashboards and reports. Its primary function is to transform raw data into insightful visuals for better decision-making.
Where can you download Power BI Desktop and is it free? Power BI Desktop can be downloaded from the Microsoft Store, or from a direct download link. It is available for free.
Name at least three different data sources that Power BI can connect to. Power BI can connect to various data sources including Excel workbooks, SQL databases, and online services like Google Analytics.
What is Power Query and why is it important in Power BI? Power Query is a data transformation tool within Power BI. It is important because it allows users to clean, reshape, and transform data before creating visualizations.
How do you rename a column in Power Query and where can you view the steps you have taken to edit data? In Power Query, a column can be renamed by double-clicking on its header and typing in the new name. The applied steps appear in the “Applied Steps” pane on the right side of the Power Query Editor window.
How do you remove a filter that you have applied to data in Power Query? To remove a filter, locate the “Filtered Rows” step in the “Applied Steps” pane and click the “X” icon next to it.
What are the three main tabs in Power BI Desktop and what is the primary function of each? The three main tabs are: Report (for creating visualizations), Data (for viewing and managing data), and Model (for defining relationships between tables).
Give a brief overview of the importance of the “Model” tab in Power BI? The Model tab is important for defining relationships between different tables or data sources. These relationships allow you to create more complex and accurate visualizations by combining data from multiple sources.
Describe what drill down is. What are the three drill down effects that Power BI offers? Drill down lets you explore data at increasingly granular levels within a single visualization. The three drill down features are: “turn on drill down,” “go to next level in the hierarchy,” and “expand all down one level in the hierarchy.”
What is conditional formatting and what are some options for displaying conditional formatting? Conditional formatting highlights data points based on specific criteria, making it easier to identify patterns and outliers. Some options for displaying conditional formatting include background color, font color, data bars, and icons.

Quiz: Answer Key

Power BI is a data visualization tool within the Microsoft ecosystem used for creating interactive dashboards and reports. Its primary function is to transform raw data into insightful visuals for better decision-making.
Power BI Desktop can be downloaded from the Microsoft Store, or from a direct download link. It is available for free.
Power BI can connect to various data sources including Excel workbooks, SQL databases, and online services like Google Analytics.
Power Query is a data transformation tool within Power BI. It is important because it allows users to clean, reshape, and transform data before creating visualizations.
In Power Query, a column can be renamed by double-clicking on its header and typing in the new name. The applied steps appear in the “Applied Steps” pane on the right side of the Power Query Editor window.
To remove a filter, locate the “Filtered Rows” step in the “Applied Steps” pane and click the “X” icon next to it.
The three main tabs are: Report (for creating visualizations), Data (for viewing and managing data), and Model (for defining relationships between tables).
The Model tab is important for defining relationships between different tables or data sources. These relationships allow you to create more complex and accurate visualizations by combining data from multiple sources.
Drill down lets you explore data at increasingly granular levels within a single visualization. The three drill down features are: “turn on drill down,” “go to next level in the hierarchy,” and “expand all down one level in the hierarchy.”
Conditional formatting highlights data points based on specific criteria, making it easier to identify patterns and outliers. Some options for displaying conditional formatting include background color, font color, data bars, and icons.

Essay Format Questions

Discuss the importance of data transformation using Power Query in the Power BI workflow. Provide examples of common data transformation tasks and explain how they contribute to creating accurate and meaningful visualizations.
Explain the significance of the “Model” tab in Power BI, focusing on how relationships between tables are created and managed. Discuss the different types of cardinalities and cross-filter directions, and how they impact data analysis.
Compare and contrast aggregator functions and iterator functions (like SUM vs. SUMX) in DAX. Provide specific examples of when each type of function would be most appropriate and explain how they differ in their evaluation context.
Describe the various types of visualizations available in Power BI and provide examples of scenarios where each would be most effective. Consider the strengths and weaknesses of each visualization type and how they can be used to convey different types of information.
Explain the purpose and application of conditional formatting in Power BI reports. Discuss the different conditional formatting options available and provide examples of how they can be used to highlight key trends and outliers in the data.

Glossary of Key Terms

Power BI: A Microsoft business analytics service that provides interactive visualizations and business intelligence capabilities.
Data Visualization: The graphical representation of information and data.
Dashboard: A visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen.
KPI (Key Performance Indicator): A measurable value that demonstrates how effectively a company is achieving key business objectives.
Power BI Desktop: A free Windows application for creating interactive dashboards and reports.
Data Source: The location where the data being used originates (e.g., Excel file, SQL database).
Power Query: A data transformation and preparation engine used within Power BI to clean, reshape, and enrich data.
Data Transformation: The process of converting data from one format or structure into another.
Applied Steps: A record of each data transformation step performed in Power Query.
Report Tab: The area in Power BI Desktop where visualizations are created and arranged.
Data Tab: The area in Power BI Desktop where the underlying data can be viewed and managed.
Model Tab: The area in Power BI Desktop where relationships between tables are defined.
Cardinality: Defines the relationship between two tables (e.g., one-to-many, one-to-one).
Cross-filter Direction: Determines how filters applied to one table affect related tables.
DAX (Data Analysis Expressions): A formula language used in Power BI for calculations and data analysis.
Measure: A calculation performed on data, typically aggregated (e.g., sum, average).
Column: A field in a table containing a specific attribute of the data.
Aggregator Function: A DAX function that calculates a single value from a column of data (e.g., SUM, AVERAGE, MIN, MAX).
Iterator Function: A DAX function that evaluates an expression for each row in a table (e.g., SUMX, AVERAGEX).
Drill Down: A feature that allows users to explore data at increasingly granular levels within a visualization.
Bin: A grouping of continuous data into discrete intervals or categories.
List: An ordered collection of values or items.
Conditional Formatting: Highlighting data points based on specific criteria, making it easier to identify patterns and outliers.

Power BI Tutorial: Data Visualization and Analysis Guide

Okay, here’s a detailed briefing document summarizing the key themes and ideas from the provided transcript of the Power BI tutorial.

Briefing Document: Power BI Tutorial Series

Overview:

This document summarizes a comprehensive Power BI tutorial series focused on data visualization and analysis. The series aims to take viewers from complete beginners to proficient Power BI users, covering essential skills from data acquisition and transformation to creating interactive dashboards. The content emphasizes practical application and encourages viewers to follow along using provided sample datasets.

Main Themes & Key Ideas:

Power BI as a Leading Data Visualization Tool: The tutorial positions Power BI as a prominent tool within the Microsoft ecosystem.
“powerbi is one of the most popular data visualization tools in the world of course it’s within the Microsoft ecosystem”
Hands-on Learning Approach: The tutorial emphasizes a practical, hands-on approach, encouraging viewers to download datasets and actively participate in the exercises.
“I’m going to leave the Excel that I’m going to be using in the description you can go and download it and walk through this with me”
Data Acquisition and Connectivity: A significant portion focuses on connecting to various data sources, highlighting the flexibility of Power BI.
“it’s going to give us a lot of different options for where we can get data from… you have a ton of options there’s databases… SQL databases… Google analytics”
Power Query for Data Transformation: The tutorial introduces Power Query as a crucial tool for cleaning, shaping, and transforming data before visualization.
“it’s going to take us to powerbi power query which is going to allow us to transform our data”
“this is the window to basically transform your data and get it ready for your visualizations”
Applied Steps in Power Query: Emphasis on the importance of the “Applied Steps” feature in Power Query for tracking and modifying data transformations.
“everything that you do every single step that you apply to transform this data is going to be right over here and if I want to … I can just click X and it is going to get rid of that”
Data Modeling and Relationships: Connecting multiple data tables and defining relationships between them is covered.
“this is especially useful when you have multiple tables or multiple excels and you need to join them to kind of connect them together”
DAX (Data Analysis Expressions): Introduction to DAX functions for creating calculated columns and measures.
“What we’re going to be using are these new measures and new columns to create create our Dax functions”
Aggregator vs. Iterator functions (SUM vs. SUMX): Explains the difference between aggregate functions (operate on an entire column) and iterator functions (operate row by row).
Conditional Formatting: Applying visual cues (colors, icons, data bars) to highlight trends and patterns in data.
Drill-Down Functionality: Creating hierarchical visualizations that allow users to explore data at different levels of detail.
Lists and Bins: Grouping data using Lists and Bins to aid in visualization and create cohorts.
Visualization Techniques: Stacked Bar Chart, 100% Stacked Column Chart, Line Chart, Clustered Column Chart, Scatter Chart, Donut Chart, Cards and tables are covered and used throughout the tutorial.
“the very first one that we’re going to start with probably the easiest one and the one that you’ll recognize the most is a stacked bar chart”
Project-Based Learning: The series culminates in a final project using real-world survey data from data professionals.
Customizing Dashboards: Demonstrates how to improve the look and feel of dashboards using themes and color schemes.

Important Ideas and Facts:

Power BI Desktop can be downloaded for free.
“we’re going to click this download free button”
Power Query Editor is used to transform data.
“this is the window to basically transform your data and get it ready for your visualizations”
Data relationships are crucial for combining data from multiple sources.
DAX functions are essential for creating calculations and performing advanced analysis.
Drill-down functionality allows for interactive exploration of data.
“what happens is is someone some stakeholder in our company is saying hey Alex we want this and we want to know we want to drill down on on this IP address”
Bins can be used to create groups of data.
Conditional formatting enhances the readability and impact of visualizations.
“it’s just better to have these simple visualizations on this table rather than just having the numbers themselves makes it a little bit more easy to read and understand stand”
The choice of visualization depends on the data and the insights you want to convey.

Quotes:

“By the end of this video you’re going to be an expert in powerbi you’re going to be creating all sorts of dashboards and kpis and reports and you’re going to be sending all of them to me so I can be really impressed.” (Illustrates the goal of the tutorial.)
“Everything that you do every single step that you apply to transform this data is going to be right over here and if I want to if I go back and I say you know I really didn’t want to rename that column I can just click X and it is going to get rid of that and take it back to its original state” (Highlights the flexibility and ease of data transformation in Power Query.)

Conclusion:

The Power BI tutorial provides a comprehensive guide for users of all levels. By focusing on practical skills, real-world examples, and hands-on exercises, the series equips viewers with the knowledge and confidence to effectively use Power BI for data analysis and visualization.

Power BI: Quick Answers and Usage Guide

Power BI FAQ

Here’s an 8-question FAQ based on the provided source material:

1. What is Power BI and why is it useful?

Power BI is a data visualization tool from Microsoft. It allows users to create interactive dashboards, KPIs (Key Performance Indicators), and reports from various data sources. Its usefulness stems from its ability to transform raw data into understandable visual formats, providing actionable insights for decision-making. If your organization uses Microsoft products there is a good chance you already have access to it.

2. How do I download Power BI Desktop?

You can download Power BI Desktop for free from the Microsoft Store. The source material recommends a download link in its description for direct access. Once on the store page, click the “Download” button to begin the installation process.

3. What types of data sources can Power BI connect to?

Power BI offers a wide array of data connection options, including Excel workbooks, SQL databases, cloud services (like Azure Blob Storage), and online platforms (like Google Analytics). Some connectors are free, while others may require an upgrade.

4. What is Power Query Editor and why is it important?

Power Query Editor is a data transformation interface within Power BI. It allows you to clean, reshape, and transform data before creating visualizations. You can rename columns, filter rows, change data types, and perform other data manipulation tasks. Every transformation step is recorded, allowing you to easily modify or undo changes.

5. How do I create a basic visualization in Power BI?

To create a visualization:

Import your data into Power BI Desktop.
Navigate to the Report tab.
Select the type of chart you want to create from the Visualizations pane.
Drag and drop the desired fields (columns) from your data into the appropriate areas of the chart (e.g., axis, values, legend).

6. What are relationships in Power BI and how do I create them?

Relationships define how tables in your data model are connected. They’re crucial when working with multiple tables, as they enable Power BI to combine data from different sources.

To create a relationship:

Go to the Model tab.
Drag a field from one table onto the corresponding field in another table. Power BI will automatically attempt to create the relationship. You can double click it to edit the relationship.

Cardinality (one-to-one, one-to-many, many-to-many) and cross-filter direction (single, both) are important properties to configure. The single filter setting in the relationship limits data transfer between tables, whereas the both setting causes both tables to act as a single table.

7. What are DAX measures and columns?

DAX (Data Analysis Expressions) is a formula language used in Power BI.

Measures: Calculations performed on the fly, typically used to aggregate data (e.g., sum, average, count). They are not stored in the data model.
Columns: New columns created in your data model that contain calculated values for each row.

Examples of DAX functions include SUM, AVERAGE, COUNT, and IF. Aggregator functions (like SUM) and iterator functions (like SUMX) behave differently, with iterator functions performing calculations on each row of a table. Date functions such as DAY can also be used in DAX expressions.

8. What is Drill Down and how can I use it in my visuals?

Drill Down allows users to explore data at different levels of detail within a visualization. To use it, add multiple fields to a hierarchy in a chart’s axis. When Drill Down is activated, clicking on a data point will “drill down” to the next level in the hierarchy, showing more granular data. Useful for presenting data with layered levels of information.

Power BI Data Transformation with Power Query

Data transformation within Power BI involves using Power Query to prepare data for visualization. Power Query Editor allows for a variety of transformations.

Key aspects of data transformation include:

Accessing the Power Query Editor Accessing the Power Query Editor to transform data can be done by selecting ‘Transform Data’.
Applied Steps Every transformation step is documented in the ‘Applied Steps’ section, enabling review or removal of changes.
Common TransformationsChanging data type Data types can be changed by clicking the icon in the column header.
Filtering rows Filter rows to remove null values or specific values.
Removing columns or rows Columns or top rows can be removed.
Renaming columns Columns can be renamed.
Using first row as headers The first row can be used as headers.
Unpivoting columns Converting columns into rows can be achieved by selecting the columns and using the unpivot columns option in the transform tab.

Salary Data Analysis and Visualization in Power BI

The sources provide information on how salary data can be analyzed and visualized in Power BI, including transforming salary data and analyzing average salaries based on various factors.

Key aspects of salary analysis discussed in the sources:

Data Transformation for Salary AnalysisSalary data often needs transformation to be usable, especially when provided as a range.
Splitting Columns: Salary ranges can be split into separate columns representing the lower and upper bounds of the range.
Data Type Conversion: Convert text data to numeric data types to enable calculations.
Calculating Average Salary: Create a new column to calculate the average salary from the range by summing the lower and upper bounds and dividing by two.
Salary Analysis and VisualizationAverage Salary by Job Title: Calculate and visualize the average salary for different job titles using a clustered bar chart.
Average Salary by Sex: Visualize the average salary for males and females using a donut chart.
Impact of Country on Salary: A tree map can be used to filter salary data by country, acknowledging that the average salary varies significantly depending on the country.

Programming Language Popularity Among Data Professionals

The sources discuss how to analyze the popularity of programming languages among data professionals using Power BI.

Key aspects include:

Identifying Favorite Programming Languages Survey data can be used to determine the preferred programming languages of data professionals.
Data Transformation The survey may include an “Other” option where respondents can enter their preferred language. This necessitates splitting the column to separate the pre-selected languages from the write-in languages.
VisualizationA clustered column chart can effectively display the count of votes for each programming language.
The visualization can be enhanced by including job titles, allowing for a breakdown of language preferences by profession. For example, it can show which languages are favored by data analysts versus data scientists.

Power BI Analysis of Survey Demographics

The sources contain information regarding the collection, transformation, and visualization of survey demographics using Power BI.

Key aspects of survey demographics discussed in the sources:

Data Collection The data was collected via a survey of data professionals. The survey collected information such as job titles, salary, industry, programming language preferences, and demographic information including age, sex, and country of residence.
Data Transformation Several transformations were performed on the raw survey data within Power BI’s Power Query Editor to prepare it for analysis. These transformations included:
Splitting columns The ‘Job Title’ and ‘Favorite Programming Language’ columns were split to separate pre-defined options from free-text entries, simplifying analysis.
Calculating average salary Salary ranges were split into lower and upper bounds, and a new column was created to calculate the average salary.
Demographic Visualizations The transformed data was used to create visualizations to analyze survey demographics:
Average Age A card visualization was used to display the average age of survey respondents.
Country of Residence A tree map was used to visualize the distribution of survey respondents by country. This allows users to filter the data and examine other variables by country.
Sex A donut chart was considered to visualize the distribution of male and female respondents and their average salaries.
Difficulty to Break into the Field A pie chart was used to visualize the distribution of how easy or difficult it was to break into the data field.
Interactivity Visualizations such as the tree map showing the “Country of Survey Takers” allows users to click on a country and see how the other visualizations change based on that selection.

Power BI: Data Visualization Techniques and Best Practices

The sources cover various aspects of data visualization using Power BI, from basic chart creation to more advanced techniques and considerations.

Data Visualization Options and Usage

Basic Chart Creation:
Stacked Bar/Column Charts: Useful for comparing different categories and their composition. These can represent customer purchase breakdowns, showing what percentage of purchases come from specific products.
Clustered Bar/Column Charts: Useful for comparing values across different categories.
Line Charts: Effective for visualizing trends over time, especially with date-related data.
Pie/Donut Charts: While sometimes discouraged due to difficulty in comparing slice sizes, they can be used to show proportions.
Cards: Display single values, like total survey takers or average age, for quick insights.
Tables: Display data in a tabular format.
Scatter Charts: Useful for identifying outliers and trends in data.
Advanced Visualization Techniques:
Combination Charts: Combine different chart types (e.g., line and clustered column) to display multiple aspects of the data in one visualization.
Conditional Formatting: Use rules, color gradients, and icons to highlight data within tables or charts.
Data bars Data bars can visually represent values within a table, making it easier to compare magnitudes.
Drill Down: Allows users to explore data at different levels of granularity within a visualization.
Gauges: Visualize survey data, showing average scores and satisfaction levels.
Tree Maps: Visualize hierarchical data, allowing users to click through different levels for more details.

Key Considerations for Effective Data Visualization:

Choosing the Right Visual: Different chart types are suited for different data types and analytical goals.
Customization: Visual elements like titles, labels, colors, and data presentation should be customized to enhance clarity and readability.
Data Transformation: Data often needs to be transformed and cleaned before visualization to ensure accurate and meaningful representations.
Interactivity: Incorporate interactive elements like drill-down to allow users to explore the data.
Color Coordination: Choosing appropriate color schemes and themes can significantly improve the visual appeal and effectiveness of a dashboard.
Clear Titles and Labels: Use clear and descriptive titles and labels to ensure the audience understands the visualization.
Summarization: Instead of “Don’t Summarize,” choosing Sum, Average, Minimum or Maximum functions to derive insights.
Conditional Formatting: Add background colors based on gradient or rules, data bars, and icons.
Drill Down: Can be enabled to present data at different levels.
Bins and Lists: Numeric and date data can be grouped using bins. Lists can group customer names.

Specific Examples and Applications

Survey Data: Visualizing survey responses, such as satisfaction levels, is facilitated through gauge charts.
Sales Data: Analyzing sales data and identifying top-performing products and customer segments.
Geographic Data: Visualizing data by country using tree maps, enabling comparisons and filtering based on location.
Salary Data: Presenting salary distributions and averages, broken down by job title, gender, and country.
Programming Language Preferences: A clustered column chart is used to display the count of votes for each programming language.

Learn Power BI in Under 3 Hours | Formatting, Visualizations, Dashboards + Full Project

The Original Text

what’s going on everybody welcome back to another video today we are going to learn powerbi in under 3 [Music] hours now powerbi is one of the most popular data visualization tools in the world of course it’s within the Microsoft ecosystem and so if your company uses any Microsoft products you most likely have access to the Microsoft Suite which includes powerbi I use powerbi for many years as a data analyst and then when I became a manager of analytics I actually switched our entire team over to powerbi and so I know how amazing powerbi can actually be so we’re going to be taking a look at several things in this long long video we’ll start with some of the basics of just creating some visualizations but we’ll dive into a ton of other things as well by the end of this video you’re going to be an expert in powerbi you’re going to be creating all sorts of dashboards and kpis and reports and you’re going to be sending all of them to me so I can be really impressed so without further Ado let’s jump onto my screen and get started all right so the first thing I’m going to do is download powerbi desktop I will leave this link in the description so you can just click on it go to it and download it we’re going to click this download free button and once we click it you can go to the Microsoft store and I already have it downloaded so when you see it uh it’ll already say downloaded but um for you you can go in here you can click download and it will download it for you I’m on Microsoft uh but it may look a little bit different for you if you’re on a different system but once that is done we are going to open up powerbi so let’s go right down here to our search let’s go to powerbi and it is going to open up for us all right so right away this is what it’s going to look like when you open it and we’re going to go right over here to get data and let’s click on that it’s going to open up this window and it’s going to give us a lot of different options for where we can get data from now some of these are free and some you need to upgrade from but you just taking a quick glance through here you have a ton of options there’s databases there’s um you know blob storages there’s postr SQL or different SQL databases um there’s Google analytics there’s a lot of places and you can go through the process to connect to that data and you can pull that data in from those data sources now for what we are doing we’re just going to be using an Excel I’m going to leave the Excel that I’m going to be using in the description you can go and download it and walk through this with me so what we’re going to do is click on Excel workbook and we’re going to click connect so we’re going to go right here in our powerbi tutorials folder and we’re going to click on apocalypse food prep so let’s click on that and it is going to connect and pull that data in now right here we have our Navigator and so if you had a lot of different sheets you can click on that and choose which ones to pull in I just clicked on it right over here and we’re able to preview the data but I can’t load or transform it yet I need to select which sheets I’m bringing in so we only have on that’s the only one we’re going to bring in so you can go ahead and load the data or you can click on transform data it’s going to take us to powerbi power query which is going to allow us to transform our data so I’m going to have an entire video on how to transform the data but I’m going to give you a really quick glance at it to kind of show you what it is so right up here it says our power query editor and this is a the window to basically transform your data and get it ready for your visualizations now you can do this in Excel if you want to and do that beforehand or you can do it here and there are lots of things that we can do in here as you can see at the top again I’ll have an entire video dedicated to just power query but let’s take a quick look at the data and see if there’s anything we want to transform quickly before we actually go and start building our visualizations so over here we have the store where we purchased it we have the product that we purchased the price that we paid and the date that we bought it now the first thing that jumps out to me is that this just says date on it um we might want to say date uncore purchased and we’re going to hit enter and if you noticed right over here on these applied steps it says renamed columns everything that you do every single step that you apply to transform this data is going to be right over here and if I want to if I go back and I say you know I really didn’t want to rename that column I can just click X and it is going to get rid of that and take it back to its original state so again I’m just going to say purchased and we’re going to enter that now this is our apocalypse food prep so this is food that we are buying for the Apocalypse um for this example and if we look at our products we have bottled water canned vegetables dried beans milk and rice and all of that stuff makes sense except for the milk U milk will not stay or last long in the apocalypse so I think what we’re going to do is we’re going to filter that out really quickly and we’re click okay and right over here again says filtered rows and so now if we scroll down there’s no milk so what we are going to do is we are going to go over here to close and apply and it is going to actually load the data into powerbi desktop so on this left hand side it immediately takes us to the report Tab and what we want to do is go right here to the data Tab and take a look at our data so again there’s our date purchased and as you can see the milk is not in there another tab that we’re going to take a look at um and again in this report tab this is where we actually build our visualizations the is where we can see the data and and change it up a little bit and change some small things about it like sorting The Columns or even creating a new column and over here we have this other Tab and is called model and this is especially useful when you have multiple tables or multiple excels and you need to join them to kind of connect them together we don’t have that but in a future video I’m going to walk through how to use this entire tab so now let’s go back to the data Tab and I want to just look at the data really quickly before we go over to the report Tab and we start building our first visualization as you can see I’ve been buying these different products in different months so this rice I’ve been purchasing in January February March and April and I’ve been buying it from three different locations because I wanted to see if I was spending less money at one location on all of the products so then I would just shop there in the future and save a lot of money or if there were specific products that were really cheap at one location but others they were cheaper at a different location so I should just buy like the dried beans at Costco but everything else I should be buying at Walmart and so that’s what we’re going to look at in just a little bit so let’s go over to the report tab right up here at the top there’s this data section so you can kind of choose if you want to add any more data now that we are here we can also write queries or transform the data like we were looking at in the power query editor window over here in the insert we can add a new visualization or a text box and then in the calculation section we can create a new measure or a quick measure and then over here we have share where you can actually publish your report or your dashboard online now over on the visualization section on this far right this is a very important area this is where a lot of the actual creating of the dashboards happen so let’s take a look really quick and we’ll get into a lot of these things as we’re actually building our dashboard so we’re not just sitting here looking and talking we’re going to be actually building and doing all right so we’re going to click right here on this drop down on sheet one it’s going to show us all of our columns now two of the things that we wanted to look at were where are we spending the least amount of money buying the exact same product that’ll help us determine where we want to shop and the second thing was should I be buying all my products at the same place or are there certain products that they’re going to be cheaper at a specific store and I should buy it there so let’s start out with the first one which we’re just going to see uh with the store and the price uh where we’re spending the least amount of money and just at a quick glance we can see we’re spending the least amount of money at Costco at $210 versus Target 219 and Walmart at 225 and that really answers our question but we want to visualize it better be able to see it in an easier way so we’re going to go right over here and we can click on a lot of these but the one that probably makes the most sense is the stocked column chart and it’s going to show Walmart Target and Costco now they’re all the same color let’s add a legend so we’re just going to drag store over here down to this Legend and let’s make this larger while we’re working on it so now we can see we’re spending the most amount of money at Walmart uh right in between at Target and then at Costco is the lowest and so right there we know that Costco is the place to go for our apocalypse food prep but is it going to be that way for every product I don’t know let’s take a look let’s put this up in this corner and let’s start a new one we’re going to need to select the product for sure and the price and probably Additionally the store as well and let’s click on let’s not do this one we need a cluster column chart that’s what we need let’s bring this over here let’s expand this quite a bit and so really at a glance this is giving us everything that we need we can see each product right here and we can see how much we’re paying per store and so for Rice we’re paying it looks like a lot more for our rice at Walmart while at Target is actually where we are paying the least now if we look at all of these it looks like for Costco the only one that we’re really paying a lot more on is on our rice but for our dried beans our bottled water we’re paying quite a bit less and really it’s pretty negligible for these canned vegetables we’re paying maybe what 60 cents 50 60 cents more per can so that’s pretty negligable but for the big ticket items um we’re really spending a lot less at Costco if we wanted to SP to save just a little bit more money we could go to Target for our rice now if I want to make this more like a dashboard and we’re only keeping these two things I’m going to kind of size them kind of like this whoops going to show you that in a little bit I’m going to size them a little bit like this so now that we have that looking good we want to change the title of both of these so what we’re going to do is go over here in our visualizations and format your visual uh and we are going to go to this General go to title and now we can name it anything we really want for this we’re going to say best store for product and while we’re in here one other thing that I wanted to do is I want to go to this visual go right down here to these data labels now we haven’t added any data labels so I’m going to click on and you’ll see exactly what it does uh it just puts the labels and the numbers above it so you don’t have to actually like hover over it and see what it is now it is actually rounding these numbers so what we’re going to do is go down here we’re going to go down to values and we’ll go down to display units and it’s on auto so it’s Auto rounding those numbers and we’re just going to say none so we can see the actual value of these numbers and we can do the exact same thing over here it probably is a good thing to do um and it just is going to visualize it a little bit differently in here but you can always change that if you want to go over here to title and we’re going to say total by store and now we you’re going to take a look and so in a matter of minutes we were able to take our data from an Excel put it into powerbi transform it a little bit then we’re able to create these visualizations that gave us concrete answers to some very important topics we now know that Costco is the place to go for basically every single product except if we’re buying rice if we want to save just a few dollars we’re going to head over to Target and that’s genuinely going to change my shopping habits for the next several years until the apocalypse happens all right all right so before we jump over to powerbi and start using power query I wanted to take a look at the data and this is the Excel from our last video called apocalypse food prep and in that video we went through and we bought some rice some beans water vegetables and milk all for the apocalypse getting prepared for that now we decided to buy some additional things like rope some flashlights duct tape and a water filter several water filters and after we purchased those uh our boss or whoever were working with there somebody decided to go and make a pivot table now in this pivot table they kind of broke it out by Costco Target and Walmart and had all the items had some subtotals as well as some Grand totals right here and then they decided to kind of copy and paste that into this and you’ll see this a lot when you’re working with uh people who use Excel they like to kind of make things like this maybe makeing them to like a table or or format a little bit differently but you’ll see stuff like this a lot so this is what we’re going to actually pull into Power query and work with now we’re going to imagine that this is all we have this is the only thing we were working with and I’ll kind of reference this pivot table a little bit but we’re going to pretend this is all we have and we want to transform it to make it a lot more usable to where we can make visualizations with it so let’s hop over to powerbi and pull this excel in so what we’re going to do is Click import data from Excel we’re going to click apocalypse food prep and click open and then it’s going to bring up this window right here now this is where we can choose what data to bring in so we can take a preview and just click on it real quick and this is the pivot table that we were looking at so it does have that pivot table so we are able to pull in just a pivot table and then we have the purchase overview where it’s kind of that formatted um thing that we were just looking at with all the colors we’re going to pull both of those in so we’re going to pull in the pivot table and the purchase overview now we could just load it or we could transform it and we’re going to click transform and that’s going to bring us to power query so let’s click on transform data so now really quick before before we actually jump into working through this and transforming it I want to show you what the power query editor looks like so if we go right over here we have our queries and these are the tables that we actually pulled in and we can click on those and kind of go back and forth between them now up top we have our ribbon and the ribbon offers a lot of functionality we have things like remove columns keep rows remove rows split columns these are all things that we’re likely to use when using this power query editor there’s also another tab called transform where there’s a lot of functionality here as well things like unpivoting a column or transposing columns and rows and using a first row as a header some of the things that we’ll be looking at today there’s also another tab called add a column and this one’s pretty self-explanatory where you can add additional columns like deleting a column creating an index column or a conditional column those are the three main ones there’s also view tools and help but we’re not going to really be looking at those today and then on the far right side we have our query settings you can do things like change the name so we call it pivot table 2022 and it’ll update right over here on our query side and we have our applied steps now our applied steps are extremely important and very very useful anytime we make any change to transform this data it’s going to be documented right here and then we can go back and look at it or we could even delete that change in the future if we want to and go back to a previous version of what we just did so when we loaded the data into to powerbi it did a few things for us it CHS The Source the navigation and it promoted the headers and then it also changed the data type so if we want to check we can actually see those things or change those things like this Source right here we can click on this little icon and it’s going to bring up the actual path where we got this file so if we wanted to change that or or it changes in the future we can come here and we can change this file path but we’re not going to do that right now so let’s click on cancel and let’s go back down to change type so I promoted these headers and obviously these headers are not correct we’re looking at this pivot table and not the purchase overview but it changed these column headers and so in the future if we wanted to we could easily change those but it did that for us and it changed the type as well so if you look right here it says abc123 all the way over here to where it just says ABC ABC means it’s only going to be text where abc123 means it could be basically anything uh text or it could be numeric so now let’s go over to purchase overview View and this is the one that we’re actually going to be working on the most but we might be looking at pivot table just a little bit to kind of reference it and see some of the differences so before we do anything let’s just take a look at how powerbi decided to take this data in so it shows this apocalypse food prep overview as kind of the First Column and that was kind of our header or the title of what we were looking at before and then all these other columns are basically column 1 2 3 4 fivs so that’s something that we’re going to want to change in just a little bit there’s also all these blank uh columns right at the top and kind of these null values as we go along and we’ll take a look at those and we kind of are going to want to get rid of some of this and just clean this up to make it more usable for our powerbi visualizations this may be perfectly fine and acceptable in an Excel but when you’re pulling it into powerbi the real reason you’re pulling it in is to create visualizations not just it to look good in an Excel so we’re going to need to clean this up quite a bit so let’s go right up top the first thing that I want to do is I want to get rid of these top r so we’re going to go to this top ribbon and we’re going to click remove rows and we’re going to select remove top rows and we’re going to select two because we have one two rows of all nulles and those are completely useless we just want to get rid of them right away so let’s cck Okay and it removed those the next thing that we want to do is these this location product and all these dates these are actually the column headers that we wanted so what we need to do now is we want to go over to transform and you want to say use first row as headers and just like that we have location products and these dates as our headers exactly how we wanted them now let’s say for whatever reason you know we made a mistake and we needed to go back we would just select remove top rows and that would be perfectly fine now you can see over here it promoted the headers but it’s also changed the data type so before if we went to before we removed the headers these were all AB 123 abc123 cuz it had a lot of different data types in there so it just kind of made a generic data type but when we promoted these headers the first thing that I decided to do was also change this data type for us giving us its best guess as to what this data type is and it decided to do this decimal so this one two is a decimal but we’re actually going to change that and all you have to do is click on This 1.2 or or the data type that it has right here for you and we’re going to click on fixed decimal number and let’s do replace current and now it’s just a little bit better so now it’s 2.70 2.5 and that’s normally how we would read uh values like this because this is money so we would normally read it to the second decimal just like that and if we have it on the second decimal for some we should probably have it on the second decimal for all of them so really quickly I’m going to go through and I’m just going to change that and it should be pretty quick so hang with me for just a second all right right that is perfect now for the purposes of what we’re about to do we don’t actually need these subtotals or this Costco total Target total and Walmart total as well as the grand total really we want to get rid of those and so what we’re going to do is we’re going to go right over here we’re going to click on this dropdown and we’re going to try to filter this data before we actually load it into Power VII so we’re going to filter and we’re going to say remove empty and let’s remove those and it’s going to take out all of those nulles if we wanted to try to filter this out by saying something like Costco total or Target total we could do that by going right here clicking this drop town on products go to text filters and saying does not contain and let’s do insert and we’re going to say does not contain and we want to say total and let’s click okay and again it filtered out all of those things so there’s a few different options that you can do if you want to filter out rows that contain either null values or specific values now the next thing that we’re going to do is actually get rid of a column this grand total column and so what we’re going to do is we’re going to click on the very top part where it says grand total we’re going to go back over here to home and we’re going to click on remove columns and it says insert that’s because we’re on this filtered rows one right here um but what we’re going to do is just insert that and it’ll insert it right there that’s totally fine we can just move it to the bottom now we got rid of this column entirely now this looks really good visually I like how this looks I I like how everything is set up the biggest thing about this is that when you’re actually wanting to use this for visualizations these columns as dates doesn’t really work too well and so what we’re going to want to do is we’re going to want to transpose this or pivot this to where these dates are actually rows so what we’re going to do is select the first date which is January 1st all the way through April 1st and we’re going to hit shift and click on that April 1 right there to select all of them at the same time and then we’re going to go over here to the transform Tab and we’re going to click unpivot columns and let’s see what this does and so now what we’ve done is we’ve basically recreated our original Excel that we had so let’s go back and take a look really quickly at that so this looks almost identical to what we have in powerbi right now and this is extremely usable and very good for visualizations and is much much better than this but again we were pretending that this is what we were given at the beginning so you have to imagine you know somebody just handing you this and you to make it much more usable for visualizations in the future which happens a lot and we actually wanted to create this we just weren’t given this now a few last things that we might want to do is we want to clean this up just a little bit we’re going to select the data type and change this to date and then we’re going to select the value and I double clicked on the value and I actually want to call this cost uh or product cost productor cost and then for the location I actually want want this to be called store so now this looks really good but I want to show you one thing really quickly on this pivot table 2022 so let’s go back here this looks very similar to how we had it when it first started one thing I wanted to show you uh really quickly and I want to click on this first one we’re going to make this our column header and then we’re going to try to Pivot or unpivot this January February March April so really quickly let’s do that so we’re going to transform use first row as headers so now we have this January February March April now if you notice these are not dates these are actually text it says January February March and April so if we go to do this and we click unpivot and here’s the columns that are created when we unpivot it it is January February March and April these are not dates so we cannot go and change this to a date because that would out because it’s actually text so it’s something that you want to look out for it’s something that you need to be aware of and you can change that in the pivot table so you want to be aware of how it actually sits and looks in the Excel or whatever data source you’re pulling from before you actually pull it into Power query to transform and now the very last thing that we need to do to finalize all of this is go over here to close and apply and once we click that everything that we’ve worked on is going to be applied to the actual data and it’s going to load into powerbi to create our visualizations so let’s go ahead and click on that and so now the data has been pulled into powerbi let’s go right down here to data and we can see the data right here if we need to transform this data again we can bring it back into the power query editor window by just clicking the transform data button and it’s going to bring us right back all right so before we jump over to powerbi and start creating our relationships and our model I want to take a look at the data in Excel we realized we were buying so many products for the apocalypse that we decided to start our own store and we have several customers and some client information down here and so I wanted to take a look at some of the columns and these tables that we’re going to be looking at first thing we have is the apocalypse store these are the things that we are selling I know it’s a very limited inventory but these are the really high sellers these are the ones that I wanted to sell so we have this product ID our product name price and production cost then we have this apocalypse sales this is how many sales we’ve actually made to our customers so we have this customer ID our customer name product ID order ID unit sold and the date it was purchased and then we have our customer information right here here are all of our clients so we have this customer ID customer address city state and zip code so now that we’ve taken a look at our data let’s go and load it into powerbi so we’re going to say import data from Excel we’re going to choose this model right here we’re going to click open and we are going to want all three of these so I’m going to click on all of them and we’re just going to load it we’re not going to transform the data at all so now the data has been loaded let’s go right over here on the left hand side to our model Tab and let’s scoot this over just a little bit and move back and we’re going to move these tables up to where it’s a little bit easier to see so right off the bat you can already see that there are these lines between these tables so there are already relationships that powerbi has automatically detected and created from my experience powerbi actually does a really good job at creating these relationships automatically but we’re going to go in and take a look at these and kind of see what everything means and then we’re going to go back and create these relationships from scratch just to make sure that we know how to do every single part so to get us started let’s double click on this line connecting the customer information table to the apocalypse sales table and it’s going to bring up this edit relationship page right here so this line right here connecting these two tables actually gives us quite a bit of information without actually having to click into to this edit relationship page what this is showing is that we have a one to many relationship and there’s only one or a single cross filter direction and you can find both of those things right down here and I’m going to walk through what those mean in just a little bit on this page you can also see the columns that powerbi decided to choose in order to tie these two tables together now for our example they decided to use the customer and customer right here from the customer information table as well as the apocalypse sales but I don’t really want want to use those specifically because on this apocalypse sales table I might remove this customer information and just keep the customer ID it may have chosen these customer columns because they have the exact same name and really the same information but I want to use this customer ID anyways so what I’m going to do is I’m going to click on that column and click on this column and then I’m going to click okay and if we go back into it by double clicking again we’re going to see that and now save that and if we did what we just did before which is kind of hover over it it’s going to show us what those two tables are joined on so opening this back up let’s go down here to this cardinality and cross filter Direction cardinality has several different options that you can choose from you have one to many one to one one to many and many to many now for this example we’re looking at apocalypse sales and we’re going apocalypse sales down to customer information now there are a lot of rows in the apocalypse sales but there’s very few in this customer information and there’s only one customer per row whereas in the ocalypse sales up here the customer can have several rows for several different orders so that’s why the cardinality is many to one now if we flip this and we say we want the customer information here and we want the apocalypse sales down here we tie that together now it’s going to flip and it’s going to say one to many now let’s look at the cross filter Direction and there’s only two options here it’s either single or both and if we choose both and we click okay this now goes from a single arrow pointing in one direction to two arrows pointing in both directions but what does this really mean so in order to demonstrate this I’m going to put this back to a single Direction and what we’re going to try to do is connect the data over here or the columns over here to the columns in this apocalypse store so let’s go over here to build a visualization and what we’re going to do is we’re going to take this customer information and let’s just say we want to look at state so I’m going to click on state right here and I’m just going to make this into a table and the customer information table is only tied right now to this sales table so we’re actually going to go over to the apocalypse store and we want to see how many product IDs are being bought in these different states so really quickly we’re going to come up here and create a new measure and all we’re going to say is this measure is the count of Apocalypse store product ID and we’re going to create that and now we’re going to select it so it’s added to that table so now what this is showing is that there are 10 product IDs which there are 10 products for each of these states but that’s not actually technically correct because not every state purchased these 10 different items if we go back to our model and we change both of these to a both Direction then we’re going to go back and see what changed in our numbers so now let’s go back to our visualization and now we can see that Minnesota actually only ordered seven different product IDs Missouri 8 New York 9 and Texas 10 this is actually much more accurate than before when you use the both option it takes these tables and treats them as if they are a single table but the single option is not going to do that and so for our example if we’re trying to connect this table to this table and one of the last things that I want to show you is this option right down here which says make this relationship active now if we don’t click list and there are other options in here that connect these things like the customer to the customer then that may be the active relationship but if I select this is the active relationship that means this is going to become the default relationship between these two tables so now let’s come out of here we’re going to click cancel we’re going to zoom in just a little bit and bring these tables a little bit closer so we can zoom in just a little bit more now we are going to go ahead and delete these so we’re going to say delete yes and delete yes so just for demonstration purposes we’re going to build these relationships from scratch so we’re going to come over to the customer information t table and we’re going to drag it all the way over here and put it on top of this cust ID or the customer ID in Apocalypse sales and it’s going to automatically create that relationship and we can open this up and as you can see it created the relationship between this customer ID and the apocalypse sales and the customer ID in the customer information it also defaulted the cardinality from many to one and the cross filter direction to single so we’re going to go ahead and change that to both and click okay and then we’re going to come over here to the product ID in Apocalypse store and drag this over the product ID in the apocalypse sales and again if we open it up it created that relationship for us it created the cardinality automatically and we’re going to change this cross filter direction to both and click okay and so on a really small scale that is how it works of course it becomes a little bit more complex the more tables that you add and the more relationships that are created but this is how you’re going to actually create the relationships in the model tab within powerbi all right so let’s take a look at our tables and data before we get started so we have two tables the apocalypse sales the apocalypse store for this apocalypse sales table we have the customer product ID order ID unit sold and the date it was purchased and then for the apocalypse store we have product ID product name price and production cost now these are joined together or they do have a relationship together via the product ID so what we’re going to be using are these new measures and new columns to create create our Dax functions so really quickly let’s go over to this report Tab and let’s drop down our Fields over here so we can see everything and so to get us started we’re going to go right up here to apocalypse sales we’re going to rightclick and click new measure and it’s going to open up this right here which is basically our bar where we can create our functions and so right here it’s automatically given us the name measure but we can change that and we’re going to say count of sales so now we can start writing our Dax function that’s just going to be the name of it and what’s going to show up right over here once we click enter so let’s go over here and we’re going to say count and as we’re typing it’s automatically giving us options it has something called intellisense if you’ve ever used other Microsoft products intellisense is their kind of autoc completion that helps you look at other options very quickly and so we’re just going to click on this count and it’s prompting us to put in a column name and so we can come down here and we can select one or or we can type it out and it’ll try to predict and help us choose which column to select so for us we’re going to use this order ID but let’s just start typing it out we’ll say order ID and then we can click on it and we’re going to close this parenthesis and click enter or you can go over here and click this check mark but we’re just going to click enter and so over on this right side it finalized that and save that and we can actually look at that by clicking on this box next to it and we want to look at this in a cable so now we can see that there are 74 sales now for this we want to see who’s buying our products we want to see what our what our client name is so we’re going to go over here we’re going to choose customer and we’re going to put customer on top of sales and we’re just going to take a look at it like this so now we can see that our number one customer is Uncle Joe’s Prep shop he has 22 orders now they have the most orders with us but it doesn’t necessarily mean that they’re spending the most money with us but we can take a look at that later the next thing that I want to take a look at is how many products we’re actually selling what are our big products that we’re selling we have 10 different items but I don’t know exactly which one is selling the best if if one is doing really poorly and getting no orders this is something that I want to look into so all we’re going to do is go right back up here to apocalypse sales again right click and select new measure and for this one we’re going to call it the sum of products sold and all we’re going to start out with is by doing sum and if this seems familiar to something like Excel you’re 100% correct it is very similar and remember these are both Microsoft products so there’s going to be similar functionality in both of them and so this Dax is going to have a lot of similarities to exactly how it has it in Excel so we’re going to do an open bracket and now what we’re going to choose is this units sold we want to sum up all of these units sold and see how many were we actually selling so we’re going to say units sold I’m going to hit tab it’s going to autocomplete that I’m going to close my parenthesis and I’m going to come over here and click this checkbox so now it’s created that measure and we’re already selected in this table so all we have to do is click the check mark and it’s going to show us that we have 3,000 total products sold and we can go through here and see what the big sellers are and probably the biggest one that I see right off the bat is this multi-tool survival night so these Dax functions that you can write can be very simple and lead to really good insights that you can use for the visualizations later on now I want to take a look at the difference between something like sum which is an aggregator function and something like sum X which is an iterator function because if you add X to some of these aggregator functions you can create them or or make them into an iterator function so you can have sum and some X or average and average X adding X onto the end of them can make them into an iterator function so let’s take a look and see how that actually works I’m going to show you the difference and then I’m going to talk through the difference at the end so really quickly let’s go back to our data and let’s go to the apocalypse store now what we have right here is we have the price and we have the production cost and we want to see how much profit we’re getting from each of these as well as we can take a look at the unit sold and see how much money we are actually making so what we’re going to do is we’re going to come back over here we’re going to go to apocalypse store we’re going to right click and create a measure and in just a little bit we’re going to be creating a new column and that’ll kind of show the difference really well so we’re going to create this new measure and we’re going to name it profit and we’re going to come over here and what we’re going to do is we’re going to take the sums we’re going to start with our sums we’re going to take the sum of the price and then we’re going to close that parenthesis and we’re going to subtract the sum of the production cost so all that does is it says if something cost $20 if we sold it for $20 and it only cost us $10 that’s $10 in profit for that item and then what we’re going to want to do is we’re going to actually want to encapsulate that really quickly because we’re about to use multiply and then we’re going to sum and now we’re going to take the units sold so how many units were actually sold at that profit that we just made so let’s see if that works and let’s click the check right here and so we have the profit so let’s click on the profit oops that’s not what I wanted to do let’s use a new one let’s create a new uh table we’re going to click profit let’s make it a table and I’m going to pull this right over here now we have our profit but I really want to know is which customer is spending the most money at my store so we’re going to come right over here we’re going to click on customer customer at the top and just at a glance we can see that Uncle Joe’s Prep shop is spending the most money at the store now what I want to show you is the difference between Su and sum X so what I’m going to do so I’m going to go back to this profit and going to copy this this entire thing and we’re going to go back here to this table now we just created a measure and we were able to break it down by each customer so let’s go back over here now let’s go up here to home and we’re going to create a new column and we’re going to call this profit underscore column and we’re going to literally paste the exact same thing into here and we’re going to hit enter and each row is the exact same thing so what it’s doing is it is going through the price and it’s adding all of it up and calculating it at the bottom it’s adding the production cost it’s going all the way down and calculating it at the bottom and then it’s going over and looking at how many units it sold and then it’s performing this calculation up here and then it gives us the total and it’s doing it for every single row but that’s not really what we wanted to show what we wanted to show is the profit for each row what we wanted to say is here’s the price for the Rope the production cost for the rope and then how many units we actually sold and then it’ll calculate that and give us the actual profit for just that row but we cannot do it by just using this sum what we need to do is use something called sum X so let’s add another column let’s go back to home say new column and now we’re going to say profit underscore oops underscore column underscore sum X and now we’re going to use sum X and hit Tab and we need to choose the table that we want to put this in so we’re going to say apocalypse sales because that’s the table that we’re looking at right here we’re going to say comma and now we need to input an expression which it says it Returns the sum of an expression evaluated for each row in a table before when you’re just using sum it’s looking at all of these combined now it’s taking it row by row so what we’re going to do is basically input the same thing as we did before I’m going to copy I’m going to paste that it’s not going to be correct I need to get rid of these sums but it’s basically the exact same equation give me just a second and let’s get rid of this sum and let’s see if this works so let’s click the check button and now this looks a lot better so what this is now showing us is at a row level this nylon rope made us 51,000 almost $52,000 the waterproof matches made us $155,000 and we can go down and look at each item and see how much that actually made us versus this profit column and so that is the biggest difference between some and sum X hopefully that made sense I know that sum and sum X and and the difference between an aggregator function and iterator function can be a little bit confusing especially if you’ve never done it before but hopefully that was a good example for you to understand that concept now let’s go back over here to apocalypse sales right here we have a date purchase now in the Dax function we have some ways that we can interact with dates and so I want to take a look at those really quickly so we’re going to go right up here and click on new column and we’re just going to leave that as column but what we’re going to say is day so there’s a few different ones we have Day dates YTD next day previous day and weekday and they all are pretty self-explanatory if you click on it let’s click on weekday day it says it’s going to return a number from 1 to 7 identifying the day of the week of a date so let’s use this really quickly and so we’re going to say date purchased and click tab hit comma and it’s going to give us a three different options basically it’s a one a two and a three um right here if you hit this button read more you can read more on it this is going to say Sunday is equal to one Saturday is equal to seven I like this one personally which is Monday equals one in my brain it just makes more sense so I’m going to click on two I’m going to close that parenthesis and we’re going to I guess I’ll say uh let’s say day of week for the column let’s click that check box and now Saturdays are equal to sixes Mondays are equal to one this allows us to see which day of the week people are buying the most products on or or which day of the week is somebody submitting their orders on and so let’s go over to our report let’s get rid of this I’m just going to move this oh jeez I hate moving stuff sometimes all right really quickly I want to show you the difference between what we just did and what we already have so we have this um date purchased and let’s make that into a bar graph and what we’re going to be taking a look at is actually the units sold so right here we have this and obviously for we don’t want 2022 we’re going to get rid of the year we only have one quarter right here we can see January February March so we can tell that January has the most sales or the most units sold in that month if we get rid of that we go down today we do have some information but we don’t know what day of the week it is it could change from month to month and it’s really hard to tell exactly what if there’s any pattern there at all that’s where what we just created comes in handy so let’s recreate this exact same thing but instead we’re going to use day of week so we’re going to select day of week in unit sold let’s drag that down move this over right here and this day of the week should be on the x-axis and it’s really easy now to see if there’s a pattern here there’s really not at least not for this fake data that we have um but just I I want these uh data labels on really quickly um it’s not easy to see if there’s any pattern again Monday has the most so maybe that that I mean it goes down a little bit and then it picks back up so maybe middle of the week is our least uh sales day our Wednesdays and Thursdays are a little bit lower than the rest and the beginning and the end of the the week tend to be the highest again not a huge pattern but you know it’s much easier to see if there is a pattern from week to week or what day of the week now that we use this weekday function and so this can be really really useful let’s go back here to our data and now we’re going to look at our last Dax function for this video let’s go up here and create a new column and we’re going to be looking at something called the if statement now if you’ve ever used Excel I’m sure you have heard of this and you can do the exact same thing here in powerbi and so we’re going to name this one order size order undor size and so all we’re going to say is if we’re going to click on this one right here we need to perform our logical test and then we want to say if it’s true what’s our value and if it’s false what is our value so what we’re going to be looking at is units sold so we’re looking at order size so we’re going to say if unit sold is greater than 25 what’s going to happen if it is true if the order is larger than 25 we want to say it’s a big order and if it’s not we want to say it’s a small order super simple we’ll close that parenthesis we’ll click okay and now really quickly we’re able to see if this is a big order or a small order and so that is all I have for you today there are a lot of other doc functions but the ones that we looked at today are ones that are very common ones that you’ll see the most and there can be a lot of of really complex and intricate Dax functions that you can create and in our project at the end of this series I will be sure to include some more complex Dax functions but hopefully this gave you a good introduction into Dax so you know how to use it a little bit better all right so before we get started I wanted to remind you that you can find the data that we’re going to be working with in this tutorial in the description you can go and download it from my GitHub now the two tables we’re going to be looking at are apocalypse sales and purchase tracker and if you’ve ever created any visualization you probably seen something like this where you’ll have the store and the price and this is the the things that we actually bought so this is the total amount of Apocalypse prepping uh equipment that we bought and we’ll put the store in this Legend right here and you’ve probably seen something like this and if you’re anything like me you’re going to be in a meeting and you’re going to be presenting this and some higher up is going to be like hey Alex Alex great but I want to you know see what things we actually bought in Target how much this cost can you create a visualization for that and you’re going to be like well I could or I could use drill down and so you could have done this in the first place uh which you should have so what we’re going to do is all we’re going to do is we’re going to say we’re going to say the product right here and these are going to be the actual things and we’re going to put it right under store now you can’t see these things right but there is a a hierarchy here so once we added this these options became available let’s take it out and all those just disappeared and then if we add it back right here they came back and so you can do right here which is click to turn on drill down you can go to the next level in the hierarchy or you can even expand all down one level in the hierarchy so let’s look at each of those really quickly so let’s click on this one it’s just going to turn on drill down mode so now if I go and I click on target it’s going to drill down into these and if we want to I can then put product under this Legend and we can see all of those things but of course if we go back up it’s going to be all broken up into this clustered column chart which is more like um this which isn’t exactly what we were going for but it works now uh let me get rid of this I actually want store in the legend now if we turn that off and we click it doesn’t do that anymore so what it does now is it just highlights Walmart it highlights Costco it highlights Target so we’re going to keep that on uh but we can also do something called going down in the next level of hierarchy so let’s click on that and so now this is going to go down to the next level down to this product level because that is the next level and now it’s going to show us each of those things but it’s going to have it broken out by the store and so it’s a completely different visualization but all within the same Realm of the data that we’re looking at and what we actually care about so let’s go back up in the hierarchy and then let’s use this one right here which is expand all down one level in the hierarchy and so this one is again extremely similar except it just visualizes it differently and now what it’s doing is Walmart rice Target dried beans Costco rice so instead of having an all uh like this one where it’s stacked on top of each other it’s breaking it down individually so this one column would become three separate columns now I’m going to minimize this right here uh I’m actually going to go back up in the hierarchy just for visual purposes now I’m going to show you one more example we’re going to use this apocalypse sales up here and this is one that I actually use all the time so the one you’ve seen you know you’ll get stuff like that as especially if you’re working with like sales and stuff but I work in operations right so I have a lot of order IDs product IDs stuff like that now this one this one genuinely I use quite often I’ll have a customer and let’s make it we’ll just go like this we have a customer and we have unit sold and let’s use the customer as the legend so let’s make this one quite a bit larger and I’ll have something like this and they’ll say okay well we want to see the order IDs that go with it cuz we want to know what orders are actually happening for each of these people obviously I’m not using this exact data but very very very similar and all you have to do is take these order IDs and slide it right under here under customer and this visualization right here is something I’ve done a thousand times because what happens is is someone some stakeholder in our company is saying hey Alex we want this and we want to know we want to drill down on on this IP address we want to drill down on this certain database we want to drill down on something and we want to see the order IDs within them so then all you do is you turn on drill mode or drill down mode you’ll click on it and you can see every single order ID that’s in there and then they can go and look those up in their system and resolve them or whatever they’re trying to do with it and it helps a ton and it’s very very useful this one is extremely applicable and that’s really all drill down is again you have these different hierarchies as well um but for different things it’s not as useful as you can see we also have this hierarchy which again is not as useful so it just depends on the data that you’re using and how you want to use this drill down effect but I promise you that drill down is used all the time especially when you’re giving presentations where people want to know more information than just the the visualization that you’re presenting all right so before we get started I wanted to let you know you can go and download the data that we’re going to be using in this tutorial in the description below is on my GitHub so we are going to be looking at bins and lists today um and for this we’re going to be going over here to this apocalypse sales uh and let’s open up our data right over here and we want to look at apocalypse sales really quickly I feel like more people would know what a bin is so we’ll kind of start with a list just go a little bit backwards than we normally would uh I’m going to use this customer or we’re going to use this customer column right here for a list really quickly and you can do that in two ways you can come up here and you can right click on on the customer and go to new group or you can come over here under this uh the Field section on the far right and go to customer right click and click new group so let’s click on that now and right now is only giving us the list type it’s not giving us bins because bins have to be numeric so we really can’t do that at the moment um so we’re going to call this just customer groups just or or we’ll actually call it list just so it’s easier to recognize when we create it and so all we’re going to do is we’re going to basically group these but it’s going to be called a list and so what we’re going to do is we’re going to select and we’re going to select and we’re going to say group and click on this group button and then it creates this Alex the analyst apocalypse Preppers and uh this prep for anything prepping store so that it kind of named it for us but if we double click on it then we can rename this and we can call this the best prepping stores and then we have these last two and we can we can click on one and then click control and click on the other one so we get both of them and then we can click group and we can call this and we’ll double click and we’ll call this the worst prepping Stores um and then that’s it and that’s all we have to do and what we’re then going to do and if you want to undo this and you want to switch it up and do whatever you can click on group but we’re not going to do that we’re going to click okay and here is the column that it created and it basically tells us what list we put it in if it’s Uncle Joe’s Prep shop that’s in the worst prepping stores list and if it’s the Alex the analyst apocalypse Preppers that is in the best prepping stores so it’s kind of like an if statement you could even create a calculated column do it on this customer create an if statement this is just a lot faster and a lot easier than doing that but it basically would do the exact same thing now you can use lists as well on things like numeric so let’s say we have order ID and we’ll go to new group and it’s going to Auto go to bin because typically that’s what you’ll use but you can do list as well and let’s say you know we want to say we want to call these like we’ll group these and call these the first um we’ll call this the first customers or the first orders because we’re looking at order IDs look at the first orders and then we will go back here we’re going on the left side we’re going to click oops we’re going to go back to the top we’re going to hit shift group all of these and we’ll say the latest orders and you absolutely can do this um again this is kind of like an if statement right so you’re saying if it falls between this range and this range then it’s called the first orders and if it’s between this range and this other range it’s the latest orders um again it’s just a much simpler version of an if statement and so you don’t have to write it all out you can just have this user interface kind of do it for you uh and and it’s really really useful so now let’s talk about bins and by far the easiest way to demonstrate this and I’ll show you one other way uh but by far the easiest way to show this is by using age and so uh for absolutely no reason whatsoever these customer IDs uh who are right here in this customer information they decided to give us some of their buyer information who are actually buying their products on their website it or in their store they just decided to give it to us as well as some uh simple demographic information I I don’t know why but what we’re going to use bins for is grouping these age brackets so you know you might be interested in say well I want to know if my core population who are buying my products are within a certain range and you don’t want to look at every single age because then it just you know in your visualizations it’s not going to look right you want to kind of group them and make it easier to visualize so what we’re going to do is going to go through here and we’re going to basically go by tens so 10 20 30 40 50 60 and see what age bracket these people fall in so we’re going to go to age we’re going to right click and we’re going to say new group and we’re going to go to bin and we’ll leave it as the default age bins um and you can do two things you can do the size of the bins which splits it uh uh which splits it by this number right here or you can go based on the number of bins so if you only want to do five different bins it’ll count calculate that for you and it’ll say okay if you only want five bins you’re going to have to do it at 12.2 if you want 10 bins it can be 6.1 but it is completely up to you on how you want to do that um you can do the size and we’ll just say every 10 which is what we’re going to do or you can go through and then you can create you know the how many bins you actually want so let’s go ahead and click okay and it’s going to create those bins for us so if somebody is 78 they’re going to be in the 70s bin if somebody’s 41 they’ll be in the 40 bin if somebody is 29 they’ll be in the 20 bin and so on and so forth so when we go to visualize this we don’t have you know 71 72 73 74 have a lot more things on our visualization it’ll just be the 70 or it’ll just be the 20 now we can also use bins on dates as well so let’s go back to apocalypse sales we have this date purchase so we can create a bin for this as well so let’s go to date purchased let’s go new group now you can also create a list and that’s totally fine if you would like to do that um and it would look kind of like this where you can go through and you can select it and you can say okay this group all these dates you can group those and say this is going to be January uh and you can do that and that’s totally okay um but for this one we’re going to do bins I think it’s a little bit easier to do bins because what we can do is go right here and we can specify what we want seconds minutes hours days months or years and so um for the data that we have it goes January February and March so we’re going to do months and we’re going to say the bin size is going to be one month so each month should have its own bin so it’ll be three bins total so we’re going to select okay and as you can see on this right side we have January of 2022 and that correlates to the January over here then it goes down to February and then it goes down to March and then when we visualize this uh we don’t have to do this the hierarchy stuff that we do in here where we filter it down down to months we can just use this right here and that will be our month’s column so now let’s go over to our visualizations and we’ll see how this looks really quickly we’re not going to look at all of them but we will take a look at few of them so the first one that we can look at is age so let’s look at the buyer ID and then we’ll do age as well and so let’s spread this out and we can see our distribution of our buyers so it looks like we have very few uh who are in the 10 range thank goodness and we can even put the age right under here under the age bins and we have this now we kind of have this drill down and so if we go right here and we drill down right there this will actually give us the breakdown so this is what it would have kind of looked like our visualization would have looked like if we had just kept it the age because now we’re drilling down into the age and so it looks like we have one 18-year-old and maybe a 20-year-old as well um um let’s go back up yeah so it looks like we only have one buyer ID yes so there’s only one 18-year-old so of legal age to start buying you know all these prepping equipment and probably uh buying online and stuff like that which makes sense right so uh this gives you kind of a quick breakdown in the bins rather than um doing it the alternative way so now let’s take a look at the customer list as well as the unit sold and it looks like the best prepping store uh is actually performing much worse surprisingly uh than the worst prepping store right so before we get started if you want to use the data that we’re using in this video you can find it in the description on my GitHub now conditional formatting is super simple and you’ve most likely used it in Excel before but you can also use it in powerbi and let me show you how to do that so the first thing we’re going to do is come over to our apocalypse store and we’re going to pull up our product name as well as the price and what we can do is come over here and we’re going to go to price and it has to be Under The Columns so you can’t come over here and do this we’re going to come right over here to price and we’re going to right click and let’s go to conditional formatting and we have background color font color icons and web URL let’s take a look at background color first this is most likely the one that we’ll look at the most so we’re going to get this pop up and I’m going to slide this over now there’s a lot of different things we can customize in here and the first thing I want to take a look at is format style we have the gradient and what it’s going to say is the lowest value will be this color highest value will be this color it’ll give us this gradient color scale and so we’ll use that in just a little bit but we can also create rules kind of like an if statement and if it is between this range and this range we’ll give it a color and if it’s between a different range and a different range we’ll give it a different color so we’ll also try that one and then we have this field value uh and this one is one that uh honestly I don’t use that much I’ve used it maybe once and what you can do is select a text field like customer and you can do some izations on the first and last and that is it so what we’re going to do is we’re going to look at gradient specifically for not the customer but we’re going to go back to the apocalypse store and we’re going to do it on the price now what I’m going to do is keep it as the count because this is what the default is and we’re going to go back and fix it later but what we want our lowest value to be is this bright green showing that it’s it’s a cheap product it’s easy to purchase the high value ones are going to be just the shade of red more expensive and we’ll do it on the count now remember the count is on each of these and we’re not doing a count of how many are sold we’re doing a count of each product so it’s just one per row so it all should be the same color let’s take a look so it is all the same color but what we really want to show is the actual price not just the count of the price so let’s go back to conditional formatting we’re going to click the background color again and this time we’re going to change the summarization now you can do sum you can do average minimum maximum it really doesn’t matter for this example the number is the same regardless of really which one we choose so we can just choose the minimum and it’s going to choose the minimum of each row which is the price so we’re just going to select minimum for this example we’ll select okay and it should correct it accordingly which means the bright green is the lowest and it goes all the way up to the highest which is the red now let’s go over here to apocalypse sales We’ll add in the units sold and let’s move that out a little bit and I’m doing that on purpose because we’re about to look at something within the conditional formatting so let’s go to unit sold and we’ll look at the conditional formatting for this one now if you noticed we now have a new one on here called datab bars now we’re able to see data bars on unit sold and not price because unit sold is something like a sum an average something that’s aggregated but let’s take a look at data bars because I want to show you how to use this and then we’ll go back to the background color so for data bars we are going to taking a look at the lowest to the highest value again we’re going to go from bright green all the way to this exact red it’s going to be from left to right and what it’s going to show you is if it is a positive number which all of these are is going to be a green bar basically representing the number that you see in here along this line so let’s click okay and we’re going to be able to see the highest numbers and let’s let’s scooch this over quite a bit so you can kind of get a better understanding and we’re going to do it from highest to lowest so we sold the most multi-tool survival knives at 477 and so this entire bar this row is entirely filled up or almost all the way filled up while as it gets lower and as we sell only 182 solar battery flashlights the bar is going to represent that and show that now I’m about to completely mess up this visualization on purpose because it’s about to get very messy to show you that you can do a little bit too much uh it is possible what we’re going to do is we’re going to go right over here to this background color unit sold and instead of gradient let’s look at rules now with the price we just did a gradient scale but we can do basically groups of these and say if a number is greater to or equal than this number then it’s going to be a certain color and then if it’s in a different range we can give it a different color so we’re going to say if it’s greater than or equal to zero and we’re going to say number not PR and if it’s less than 266 cuz we have 265 right here let’s make it a nice uh like gold a beautiful lovely mustard gold just just great now we’re going to say if it’s greater than or equal to we’ll do 266 because this is less than 266 so it should be greater than or equal to 266 number and if it is less than we’ll say 500 now we want to do this this one and we’ll give it uh let’s do like a peach and we’ll click okay and now we have another conditional formatting on top of that that can give us more information now again you should not do this it’s just too many now let’s go one step further and make it even more ridiculous and show you one more thing before I show you how you may actually want to use this uh let’s go back to unit sold we’re going to right click go to conditional formatting and you can do something called icons um font color is the exact same thing as back color except it changes the the font and so I’m not really going to look into that one icons are very simple extremely similar to Excel and how you’ve seen them and the rules that you can apply to them are basically the same as if you’re doing like a gradient and it’s these if statements that we saw before now it autog gives us this right here which basically says 0 to 33% 33 to 67 67 to 100 if it’s in the bottom 3% it gives us this red the middle is yellow and the top is green green so we can go through and change all of this but honestly this looks pretty good so let’s click on it and so the ones that are least sellers are these red ones right here and the top sellers are up here now this is just based on unit sold and this looks absolutely terrible so let’s kind of take this exact information but make it a little bit better so we’re going to create a new visualization or at least a new table so let’s click on product name and we’ll take the price unit sold and revenue and what I think makes the most sense for looking at revenue is these data bars right here but there’s only one problem I can’t do that because it’s not summarized like unit sold was but what I can do is to get that those data bars is I can come right down here instead of saying don’t summarize I can summarize it and I can just click the sum so it now is summarized it’s the exact same number but if I right click on here as sum of Revenue I go to conditional formatting I can now use those data bars and so we’re going to use those data bars and we’re going to say for the lowest value and the highest value and let’s just make it a nice maybe a darker green I don’t want it to well that’s that’s hideous let’s make it this color right here a nice dark green and there’s no negative so it doesn’t really matter we’re going to go left to right and you can show the bar only but we’re going to keep it because I want to see it and we’re going to go just like this we’re going to order and this is pretty telling um honestly I did not think the weatherproof jackets were performing so well but I mean they are by far a number one seller so you know our weatherproof jackets multi-tool survival knives and the nylon rope are perform outperforming all of our other products so those might be the ones that I focus on the most while duct tape the n95 masks and waterproof matches I mean those are those are garbage so I might be looking to replace those in the near future with some other items that might sell a little bit better so that’s how you use conditional formatting and it’s actually pretty useful there are a lot of times where I’ve done something like this in an actual visualization for work and it looks something like this it just depends on what you’re visualizing but this is very much a simple thing that you can do to just add a little bit more information and and actual visuals to this little chart or table that you’re going to create sometimes it’s just better to have these simple visualizations on this table rather than just having the numbers themselves makes it a little bit more easy to read and understand stand all right before we jump into it there is a link in the description where you can get the data that we’re going to be using for these visualizations if you want to practice them yourself before we actually get into it we do need to combine this and if you download that Excel and you see this you’ll have to do the same thing all we have to say is that this product ID is the same as this product ID purchased and now we are good to go do one to many and it’s okay if it’s one way so right over here under this visualizations tab there are lot lots of different options and it can be a little bit overwhelming you don’t really know which one to choose there are some in here that I have almost never used for my job ever so I’ll Point those out as we go through but the main focus is going to be focusing on the ones that I do use that I have used and showing you how to actually create that visualization maybe spice it up just a little bit but we have a lot of them to go through so let’s jump right into it and the very first one that we’re going to start with probably the easiest one and the one that you’ll recognize the most is a stacked bar chart and what we going to do is go ahead right over here to the product name and we want this unit sold as well so we’re going to click product name and it’s going to go straight into the Y AIS for us and then we’re going to click unit sold and that will go into the x axis automatically it just kind of intuitively knows but sometimes it will make a mistake and then you can just fix it or flip it and we do want this uh let me make this much larger we do want this to be a little bit more colorcoded that is what this Legend is down here so what we’re going to do is drag this product name down to the legend and now we have each product as its own color and in previous videos we have gone through and looked at some of these Visual and general options that you have when you’re actually creating these visualizations but we’re going to do some of them while we’re in here as well so we’re just going to go down here we’re going to choose data labels and we’re going to shrink that and if you go higher the higher you go the less you see so if you want all of them all the way down to the green we’re going to go right about there and we’re going to make it smaller so now we can go ahead and click anywhere outside of that visualization and now we can create a new one if we had just kept it like this where we were still interacting with this visualization and we clicked on a different one it would have then changed our visualization completely which we don’t want so let’s hit contrl Z click out of it and now we can create a new one let’s go right over here to this 100% stacked column chart I’m going to click on it try get get over here and make it much larger and we’re going to come right over here to this customer information and we’re going to click on customer and then we’re going to go up to unit sold and click on unit sold and we want to break these out and so basically what this is doing is it’s breaking it out by each of these shops and we can see the total of what they’re buying the units sold but we want to see exactly what products make up this percentage or this 100% so we’re going to go right over here to product name we’re going to drag that down to the legend and as you can see now we have each of these products and each of the products is up here so this backpack we can see the backpack right here backpack right here and right here and we can see which customer is buying what percentage of their purchases so for this prep for anything prepping store they have a very large percentage 40% is duct tape so they’re buying a lot of duct tape so really quickly we’re able to see what clients are purchasing or which clients are purchasing what products the most so so just like this Alex analyst apocalypse Preppers they’re buying a lot of water purifiers we like drinking clean water um you know that’s just what my audience likes and so you know we can easily get a quick glance of that again we’re going to go in here I tend to like putting these data labels on here that’s just what I preference so you know something like this it looks nice it looks clean um we can always go back and change these names which we’ll do for this one so we’re going to go over here go to title we’ll go down to to the text and we’ll do customer oops customer purchase oh jeez breakdown pretend I’m really good at spelling and we’re GNA do it just like that we’ll get out of there so now we have customer purchase breakdown and that looks really nice it’s a good uh a good visualization and we’re going to bring that right over here we’re going to have a lot on the screen so I may have to uh make them smaller or larger to fit everything all right so let’s go on to our next one another really common visualization is this one right here which is the line chart and the line chart is great especially when you’re using things like dates I have found this one to be the best and a lot of people use this as well so we’re going to go right over here and click on date purchased and then unit sold and on the x-axis you can see it’s broken up by year quarter month and day so we don’t want to do at that high level we only have three months of data in here so we’re going to get rid of the year we’re going to get rid of the quarter and then we at least have this and let’s break it out cu right now we’re looking at all of the units sold so we’re going to drag the product name right down here to the legend and now it breaks it out by the actual product and for each month in January February or March you can follow these products and see how they did in each of those months and if we wanted to we can come right over here to the filter on the product name and we could filter it by maybe the top three so let’s do multi-tool survival knife the nylon rope and the duct tape we can have it just like this and you know you can do those for any product that you want but again we just want to do it for those three just for an example and that really doesn’t give us a ton of information we could even go down to the day and you know it might give us a little bit more information and so we’ll keep it like that and we can go over here change the name as well we’re not going to do for all of them again we’re just looking at the different types of visualizations I think are really good to know but we’ll change this one as well to products purchased by date we’ll keep it just like that again nothing fancy we’re just trying to look at a bunch of different stuff so let’s put this over here down here now let’s click out of there and there are other ones in here um that are definitely useful and you absolutely can use um like this one is a stocked bar chart this one is a stacked column chart it’s basically the same thing just a different orientation we went to here it’s just a different orientation it’s the same thing um just like this clustered bar chart cluster column chart it’s just its orientation either horizontal or vertical then we have things like an area chart a stacked area chart not really things that I’ve used too much in previous positions one that I have used though is a line and clustered column chart so it kind of combines a few of these with you know you have these bar charts as well as line charts into one visualization so let’s look at this one because this is one that I have used several times in my actual job so for our x axis we’ll use the product name then we’ll look at something like the price and so let’s make this a lot larger so you can actually see it so now we have the price and now we can look at something like the production cost and that can be our line Y AIS so now we’re looking at the price of it how much someone is actually paying for it and then we’re looking at how much it’s costing us to actually produce that product and so really quickly at a glance you can kind of see that it’s around the halfway to 2/3 point on most of these you can see that the production cost is always lower than the actual price because of course we’re out here to make a profit on these products so let’s minimize this one we’re going to put this one right down here let’s make it even smaller smaller let’s click out of that and the next one that we’re going to take a look at is a scatter chart so let’s click on that and make it much larger oops there we go so let’s use the price and the production cost again and so our x axis is the price our y AIS is the production cost but now we need to fill in this values right here so let’s go over here and click on the product name and drag that into values and so now we have our values we just don’t know what they are but we can see see it so let’s drag this down to Legend as well and it breaks it out and we kind of have this scatter plot and you know for this fake data that we’re using it doesn’t really show a lot U but if you’re using real data you can definitely find outliers and Trends and patterns using this type of visualization let’s go ahead and make that one small as well iy get right down into the corner now let’s go right over here and we have the the dreaded pie charts um and donut chart now look I think it’s kind of a joke in the data analyst Community about pie charts and dut charts but at the same time people use them and they request them and so sometimes you’re going to use it whether you like it or not so let’s click on the dut chart and let’s make this one a lot larger and let’s go over here and let’s click on State and we’re also going to click on total purchased and that’s really all you have to do these ones are pretty straightforward you can change a few different things like where these labels are if you want them inside you can also do that that would look totally fine um again I’m just not a super huge fan but you will get this one requested people like this and want to see it and the reason a lot of analysts don’t like using this is because when you start glancing at these it’s really hard to tell the difference between these sizes if you look at something like this you can easily see that this is larger like if you’re looking at this one the multi-tool survival knife is obviously the longest and it gets shorter shorter shorter shorter but when you start getting in here it’s really hard to approximate the size I would not be able to tell the difference between this 5.63 5.78 two 7.72 I would not be able to tell really the difference between these or or kind of the the difference between them very easily that’s why a lot of people don’t want to use them in general so again I want to show you this one because I think it’s worth noting and worth knowing how to use but I don’t really push people towards this because I don’t think it’s the best visualization available most of the time all right the next two are super easy but are used all the time uh maybe more than some of these even but they’re just so easy to use so I’m kind of saved them for last this one is the card and all the card is is it displays one number or multiple numbers if you want to use a multi- card but we’ll just look at the card for now all we’re going to look at is the total purchased and it’s just going to display it just like this and you can make it as large or as small as you’d like and normally it goes on like the top and you’ll put card here a card here um just for example I’ll kind of show you how this might look so it look something like this right and at the top it’ll have different usually High overarching information and this is super common to see and I’m sure if you’ve looked at other people’s visualizations you’ll see something like this this is usually totals or averages or something like that in here where it’s super easy to look at so like right here this is total purchased and we can go in and look at the minimum and then we can go over here and this one can be account and so it gives us a lot of information just at a really quick glance and then we have all of our more in-depth colorful visualizations that kind of have more information than just a single piece like the card does and then the very last one that I’m going to show you is this one right here which is the table and this one is obviously extremely popular it’s like an little Excel table and we can go in here and we can get the customer wherever that is and then we’ll also get the unit sold and this is what it looks like and it’s super easy and often times you’ll have it like on the side as well uh and all the other visualizations over here and so you know if we’re going to take all these visualizations and pretend they were like a real thing you know there’s a lot in here but we’ll just kind of really quickly do this um you know we might have something like this and we’ll make this larger and make this wider and you know we have a lot of information just in here and this is not a project so don’t go put this on your portfolio I’m just threw a ton of random visualizations on you know this dashboard but you can already see a lot of these you most likely have seen in other people’s work and other people’s visualizations on LinkedIn or on YouTube these are very common very very popular and again we did not go through all of the ones over here there are maps that you can use but I haven’t used Maps ever in my job there are things like gauges and deom composition trees and waterfall charts and uh tree maps and all these different things but I really have never use those in my actual job and I don’t see them a lot in others people’s work either otherwise I would be telling you to learn these and use these what’s going on everybody welcome back to the powerbi tutorial Series today we’re going to be working on our final [Music] project now this is our final project of the powerb tutorial Series so if you have not watched all of those videos leading up to this I recommend going and watching those videos so you can make sure that you know all the things we’re going to be looking at in today’s project I am really excited to work on this project with you because I think it is a really good one and it uses real data that we collected about a month ago where I took a survey of data professionals and this is the raw data that we’re going to be looking at and so I think it’s just really interesting that we collected our own data and now we’re using it for a project we’re going to transform the data using power query and then we’re actually create the visualization and finalize the dashboards as well as create a theme and a different color scheme to kind of make it a little bit more unique without further Ado let’s jump on my screen and get started with the project all right so before we jump into it I wanted to let you know that you can get the data below it is on my GitHub you can go and download this exact file that we’re going to be looking at now in the past several projects we have been using this fake apocalypse data set you know it was fun it was you know whatever this data set is real this is a real data set it was a survey that I took from data professionals I posted on LinkedIn and Twitter and all these other places and we had about 600 700 people who responded to the questions so before we actually get into it and start cleaning the data and doing all this stuff in powerbi I just wanted to show you the data all right so this is the CSV that I downloaded from the survey website that I used and this is completely raw data I haven’t done anything to it at all but let’s go through the data really quickly and we’ll kind of see what we have and we are not going to make any changes at all in Excel we’re going to do all of our Transformations or at least a few transformations in powerbi because again this is a powerbi tutorial and project so I want you to kind of learn how to use that and not use Excel because you can go through my Excel tutorial if you want to do that so let’s just look at it in Excel and then we’ll move it over to powerbi and actually start transforming the data so we have this unique ID these are all the people that actually took it oops don’t want to do that we have an email which this is completely Anonymous I didn’t collect any data or user data on this then we have the date Taken um and let’s get into the actual good information then we have all of these questions so we have question one which title fits you best and they can choose things now uh let’s add a filter really quickly that we can look at this now you had the pre-selected ones which were like data analyst architect engineer but then there was an option where you could say other and you could specify what that was so if you look in here we’re going to have all all these different other please specify with different titles right and there were a lot of them now typically what you want to do is really clean this up and we’re not going to be doing a ton ton ton of data cleaning but we are going to do some in powerbi but none in here but typically with this amount of data and the way that it’s formatted we would do so much data cleaning um with this one I mean there is a lot of work to be done um like this current year salary this is one that I would absolutely be cleaning up because it’s arranges and it has a dash and a k and all these numbers this is something that I would be cleaning up and using but we’re not going to be cleaning this up right now so anyways let’s just get into it let’s see what questions we asked uh we have the yearly salary what industry do you work in favorite programming language then there were a lot of different options this is like one question where they picked multiple options so is how happy are you in your current position with the following you have your salary work life balance um then we have co-workers management upward Mobility learning new things um and they could rank it from zero to 10 so some people ranked upward Mobility at 10 so I’m ranked it a zero or a one um and again they can answer however they want how difficult was it to break into Data very difficult very easy um if you’re looking for a new job we have have you know what would you be looking for remote work better salary Etc we have male female which country are you from and then this is more like demographics so if you’re a male how old you are and this was in a Range so this is like a a a a sliding bar so you can slide to the exact age you had there’s some people who are apparently 92 um which if that’s true I mean good for you man or woman actually really quickly I’m going to see just just while we’re here I’m going to see if this is a male male or female that’s a female from India very cool um so we have all this information and it is a lot of information when you have something like this I mean there is so much data cleaning that can be done I mean I already see like 20 plus different things that I would need to do to make this a lot better um and we also have date Taken and the time taken as well as how long they took on it like the time spent really really just really interesting data but again this is a beginner tutorial Series this is the beginner project so we’re not going to get do anything too crazy I will be using this exact data set in a future video doing a lot more data cleaning and creating a much more advanced visualization with what we have and what we’re looking at right here but for this video we’re just going to be doing a pretty simple visualization and dashboard that you can use uh to practice with or put on your portfolio if you know that’s where you’re at right now so let’s get out of here and let’s put this into powerbi so let’s exit out and let’s come right over here to import data from Excel we’ll click on powerbi final project and open give that a second doing this all in real time we only have the one so we’ll do be we won’t be practicing any joins or anything but we’re not going to load it we’re going to transform this data so let’s put it into Power query editor and now we have all of our data in here and it should look extremely familiar now when I’m looking at this when I start looking at this information I kind of need to know beforehand what I want to get out of this do I need to clean every single column do I just need to clean a few of them do I need to get rid of columns that’s kind of where my head’s at and so right off the bat I can already tell you that there are columns that we can just delete to get out of our way so we’re going to do that at the beginning so that we don’t have to do that later on or they’re just in our way so I’m going to click on browser and then I’m going to hit shift and I’m going to go over here to refer and I’m just going to go up here to remove columns and everything that we do is going to go over here to this applied steps if you’ve been following this series um you know we can remove things add things but anything we do will show up right over here so we can track it and go back if we need to now one column that I know for sure that I’m going to be using quite a bit is this which title fits you best in your current role because I I specifically wanted to do a breakdown of diff people’s roles and how much they make and different stuff like that so I know that I want to use this but as we saw before there’s kind of the issue is is it’s not very clean right it has data analyst data architect engineer scientist databased developer and then all like a hundred different options and then a student or or none of these right um and so for the purpose of this video right here we are not going to take every single one of these options because this involves a lot more data cleaning let me give you an example this says software engineer this also says software engineer and with AI these two would typically be combined or standardized to software engineer but it’s not very easy to do that in powerbi we could do that in Excel but not really in powerbi or even SQL if we pull this from a SQL database um and you can find lots of different you know options of that we have data manager and data manager if we separated these out these would be different options when we created our visualizations and we don’t want that so what we are going to do uh and this is going to be kind of a an easy way out to just make sure that this is pretty clean and doesn’t we don’t have a thousand different options we’re going to create this to other so we’re going to simplify this a lot and then we’re going to use this so we’ll have maybe six or seven options instead of the you know let’s say 50 that we would have if we actually did the harder work which just break it out standardize it and clean it up that way so what we’re going to do is we’re going to click on this right here and we’re going to go up here to split column in this ribbon up top we’ll go to split column and we want to do it by a delimiter and if you notice let me see if I can move this over if you notice we have other and then we have this parentheses and in no other option way is there parenthesis so what we’re going to do is we’re going to use a custom and we use this open parentheses what that’s going to do is it’s going to separate it by this parentheses it’s going to leave the other it’s going to create separate columns um just one separate column for each of these and we can do that at each occurrence or we can do the leftmost and we really we only need it for the leftmost because there’s only one of these uh left-handed or left-sided uh brackets or or whatever this is called and then let’s go and click okay and it should create another column so it’s going to have 0.1 point2 and now we have if we click on this now we only have these options we have analyst architect engineer data scientist database developer other and student looking or none that is what we want it makes it so much simpler and it’s not perfect but again I’m trying to show you what we are able to do in powerbi so now we’re just going to remove that column and we’re going to go and do the exact same thing to this one as well cuz I know that we want to use this and I really wanted to use this one as well but if we look at this one also um there’s a lot so I said what is your favorite programming language and people there were pre-selected answers like JavaScript Java C++ python R things like that and then there was other option and in this other option I mean it was free text so they can fill it in as they want I mean there’s four five six different ways that people put SQL that is something I would standardize and you know that would be the way I cleaned it but that’s not how we did it in here so we’re going to do the same thing we’re going to keep that other so we’re going to split this column again we use a delimiter and for this delimiter though we’re going to use a colon so we’re going to say we’re going to do a colon right there just do the leftmost we’ll click okay and then we have our options and it’s much simpler now I really would have rather kept all these and because sql’s in there quite a bit but you know a lot of people don’t think SQL is even a programming language so uh we’re going to delete that column now one that I just skipped and I kind of wanted to go back to is this current yearly salary I really want to use this let’s see if we can use it I here’s what I want to do with it and this is not perfect um but for this video I want to try it what I want to do is break up these numbers 106 25 and then take the average of those numbers so then we’ll use some docks in there so we’ll take 106 125 create that into two separate columns then we’ll create a third column that will give us the average of those two numbers so we’ll do 106 plus 125 divided by two and then we’ll have the average of that now that is not perfect but it’s going to give us at least you know an average a kind of roundabout number because they gave us this range they said my salary is between 106 125,00 000 so if we say that their salary was 112,000 at least gives us it makes it usable it’s a numeric value instead of being this which is text which we really we could use and and I’ll show you how to do that because we’re going to keep this column I’ll create a copy of this and I’ll show you the difference between this and using the average but for but for this data cleaning portion let’s just try it let’s see what we can do and see if we can make it work so first let’s create a duplicate so we’re going to uh duplicate the column so now we have this copy at the very very end and we can use this one instead of having to use the original way way way back here so we’re going to leave that one how it is and we’re going to use this one so let’s go ahead and split this one up we’re going to click on the column header then we’re going to click on split column and we’ll do it by digit to non-digit and if you look at it right here it’s broken it out kind of um in the fact that now in this one we just have numeric values and in this one we have k-h numeric or just Dash numeric and now this can be easily cleaned whereas this one we can just completely get rid of because it’s only K so we’ll just remove that column and then in this one we’re going to rightclick we’re going to click on replace values and so if it just has we’re just do a k a we’ll replace with nothing do okay and then for the last one we’ll go to replace values and we’ll do it the dash or the minus sign and we’ll place that with nothing and so now we have our values as well oh we also have a plus let me get rid of because that’s when some people had 250 or 225,000 plus so for that one the average is just going to be 225 we’ll have to specify that in our decks I forgot but actually if somebody has 225 let me find this plus really quick uh let me filter by it because that’s a lot faster what we actually want to do for the purpose of this one is we want to put 225 here so that when we do 225 plus 225 divide by two it comes out to 225 that’s just what we’re going to put it as and there’s only two people so uh I’m actually going to replace this I’m going to do replace values I’m going to say plus with 225 and we’ll click okay on awesome we can unfilter these select all so we’re going to go right up here to add column we’re going to say custom column and we’re going to go right over here actually let’s make it uh average salary so we get average salary so we’re going to insert this we going to say parentheses and we’re going to say plus this insert and close the parenthesis divided by two and it says no syntax errors have been detected let’s click on okay and it’s giving us an error so it’s saying we cannot apply operator plus to types text and text which makes perfect sense these aren’t uh numbers so let’s make it a whole number and let’s make it a whole number and then let’s see if this will actually work now or maybe we just need to try a whole another one so let’s try transform or add column custom column let’s try this all again see if uh I can make it work insert this one plus this one and we’ll do divided by two and let’s try this one and there we go so now let’s get rid of this column columns and we can actually remove these ones as well because now we have this um average salary column which when we look at this or when we use this uh we can let me see if I can just move this way way way over all right I might cut because this taking forever so if you take the average of these two numbers you’ll get 53 if you take the average of 0o and 40 you’ll get 20 so now we have this average salary and again when we get to the actual visualization part I’ll show you why this isn’t as useful as having this average salary and just a reminder this is not perfect uh I wouldn’t typically do this especially if I had it in Excel or if I was you know creating this survey in a different way I would probably have a very specific value where they can do it on a slider but this is how it is so we’ve at least made it usable or more usable in my mind and we have a few other things that we can change like what industry do you work in where we can break this one out so I’m going to go ahead and break this one out as well as this one right here which country do you live in I’m going to breako both of those out to where it’s the country or other I’m not going to have these other values although there are a lot of them because there’s a lot of people who live in these different countries but we can’t really do that super well in here because again the same issue kept happening Argentina Argentina Argentine a Australia so we can’t normalize those values unless we spend just a copious amount of time doing do that so I’m going to go ahead and do these I’m going to fast I’m going to fast speed this so it goes a lot faster so I’m just going to go silent and let this happen really quick and then we’ll get to the end and we’ll actually start building our visualizations all right so we’ve split them up and as you can see we have all these options as well as other and I think you know there let me tell you there is so much more that we could do with this I mean just so many other things but this is like what the bare minimum of what we need for this project so let’s go ahead and close and apply this and if we need to come back at any point and actually fix anything or change anything we can so it’s not like that’s permanent um so as you can see we have everything over here we have all of our data as it is transformed in here as well and now we can start building out our visualization so let’s go back to our report and let’s start building something out all right so let’s add a title to our dashboard make this right at the top call this the data professional survey breakdown and let’s make that quite a bit larger make it bold why not and we’ll put that in the center and now let’s um let’s add some effects let’s change that background to something like it’s too dark something like this and I do not like that bold let’s take that off there we go so something like this just has a quick title to what we’re about to do what we are about to build so we’re going to start off with the most simple visualizations that we’re going to do and we’ll kind of work our way towards kind of the harder ones so the first one that we’re going to start off with is a card and the cards are obviously like just super super easy they usually just display one piece of information so we’re going to go right over here to the very bottom at the unique ID and we’re going to select it and we’re going to say a count of distinct or a count it doesn’t matter um and it says 630 count of unique ID now we’re not going to keep that as is we’re actually going to go right over here we going to say rename for this visual and it says count of unique ID but we’re going to say count of survey takers and you can say whatever you want here but in in general that is what it is we’re we’re counting how many people um you know took this survey and that’s just a kind of a total maybe I say total amount or of survey takers but you can say count of survey takers how many people took the survey so let’s click out of there let’s click on card let’s make it about the same size size we’re going to drag it up here and try to make them about the same we will in a little bit we’ll make them the same size um but for this one we’re going to look at age so we’re going to look at current age so I’m click on that and we’ll say want the average age so our average age taker is almost 30 years old so let’s go right over here we’re going to say rename for this visual we’ll say average age of survey this might be too long average age of survey taker again name it whatever you’d like so again these are meant to be high level numbers so when somebody’s looking at your dashboard they can just really quickly glance at this and know exactly what it is instead of like some of these other visualizations that we’re about to create they don’t really have to dig into it look at the x- axis the y axis the the different uh Legend colors and whatnot they can just see these high numbers and get a really quick glance of the data now let’s create our first visualization and what we’re going to do for that one is a clustered bar chart so let’s go ahead and click on the clustered bar chart and create as small or as large as we’d like and for this one we’re going to be looking at the job titles now remember we kind of change the job titles or you know uh transform those if you want to say that so we’re going to look at Job titles and then we’re going to look at their average salary and if you remember we transformed that one as well we have average salary now this one is it looks like a text right now so it may not work properly and what we’re actually going to do is go over here I want to see the average salary so let’s click on average salary and see if we can change this data type from a text to a decimal number let’s click yes I forgot to do that when we were transforming it and there we go this is perfect um so now we can go back and we can select our average salary and as you can see it has this um this function symbol so now we can click on it and it’ll look a lot better and although this says average salary as the title it’s actually doing a count or the sum so we can click average right here and what we want to do is actually break this down by the job title and so now we can see data scientists are making the most by far they’re making average of 93,000 at least from the survey takers that took it then we have our data Engineers making 65,000 data Architects are making 63 and then we the data analysts data analysts are right here making 55 so again we had 630 people take this survey and so the vast majority of them were data analyst so this one’s probably the most accurate out of all of them and I actually don’t like how this looks as the clustered bar chart let’s try the Stacked bar chart and put this as the legend that’s more more what I was going for I don’t know I didn’t want as skinny because when you’re doing this one it typically they have multiple options per um uh x axis and so I think that’s why it was that little skinny line but this one is more what I was looking for but let’s make that smaller and let’s definitely change that title because good night um this is like incredibly long let’s go over here to this format visual we’ll go to the general the title and we’re just going to say average salary by job title just like that and this looks a lot better now we’re not going to kind of format all our whole dashboard yet we’re going to create our visualizations and then we’re going to kind of organize everything and kind of play Tetris with it to make it look the best so we’re just going to minimize this and put it right up here for now um but we will go back and kind of make everything look better at the end and actually while we’re here I also want to change this as well so rename for this we’re GNA say job title Oops why did I do that job title and for this one we’re just going to say name average salary there we go looks much better much cleaner uh took away a lot of the anxiety that I was feeling about two minutes ago when we first put that up there so let’s go on to our second visualization the next one that I’m interested in is actually what programming language people were using the most so we have salary there’s a thousand different things we can look at in here but I want to know you know what is people’s favorite programming language so let’s take a look at that so we have favorite programming language let’s find that so we have our favorite programming language and we also have how many people actually took it or the unique people so right now this is columns we don’t want that let’s um let’s do a clustered column chart click on this right here and it looks like here we go that is kind of what we’re looking for and instead of count of unique ID we’ll say count of let’s do count of Voters and for favorite programming language we’ll say favorite oops programming language and get rid of that as well and then we’re going to go into here also and change the title and say favorite programming languages or favorite pro programming language just like this now let’s make this a lot bigger so you can see it but really quickly at a glance you can see python is by far the most popular are other C++ JavaScript Java now all we’re seeing is the count so it’s all the same same it’s just blue we can see how many people voted for each one but if we wanted to break it out similar to how we did with the job titles we could still do that so all we’d have to do is break it out uh bring this job title down to the legend it now breaks out like this and that’s not exactly what I was going for I was going more for something like this where we can see the still the whole count but now we can see who is actually voting for these things so I’m just not a huge fan of the colors that are pre-selected here and kind of the whole whole theme of this dashboard at the very end we’re going to completely revamp this change a bunch of colors the background and make this look a lot nicer rather than just the white background like we have it um and so for now let’s just make this a lot smaller and put it into this corner these will not be staying there but we need to we need room to create our next visualizations and just a cleaner space to do things now the next thing that I really want to include is a way to break break down where they’re from their country because especially something like salary is very dependent on your country whereas the average salary in the United States for a data analyst may be like 60,000 in another country it could be 20,000 that could bring down the average quite a bit so we need a way to be able to break that down now we can do something like a filled map and there’s no problem with that at all um but you know for what we’re building what we’re creating it’s not probably going to work out the best I mean this looks okay we could stick it in the corner or something um and you can do that and that’s perfectly fine I think what I’m going to do is something like a tree map which I don’t use a lot but I want something where they can just click on it they can look at the values distinct they can look at the values and just click on it and it’ll be right there for them so they don’t have to filter it out on their own or no geography and look at this map they can just read C other United Kingdom India United States and click on that and so for example let’s click over here on United States the numbers change quite a bit now the average salary for a data scientist is 139,000 for data analyst it’s 80 and if we look at India you know the average salary for a data scientist is 68 the average salary is 26 for a data analyst that doesn’t mean that they make less money in India that just means that the cost of living is probably lower in India therefore they don’t need the higher US dollars salary because again this was all done in US dollars so just something to think about let’s click out of that so we’ll keep that one as well so now let’s create our next visualization and this is one that I do not get to use enough in my actual job so we’re going to use it in this project um and it’s going to be this gauge right here so let’s add that one put it right over here we’re going to add two of those let’s just go ahead and add another one while we’re at it we’re going to have them kind of like right here right next to each other the first one and these ones are really good for kind of looking at these kind of surveys and I don’t get to work with surveys enough but we can see you know how happy are they in terms of work life balance so we can add that we’re going to add work life balance um and right now it’s doing a count and if we don’t have minimum or maximum values in there yet so it’s going to look kind of weird but we’re going to look at the average rate or the the average score of these then we’re going to pull this over to the minimum value and we want to put that at the minimum and pull this over and add the maximum value so now it actually has 0 to 10 and it shows that the average person is happy with which one was this their average person is happy with their work life balance uh they rate about a 5.74 overall now let’s really quickly change the title of this because this is ridiculous I want to say happy with work life balance and this is their rating uh you know change it to whatever title you want that’s what I’m going to do and we’ll also do happy with their salary let’s click on salary we’ll add that to minimum and we’ll add the maximum value as well to make sure that we know how to use that and then we’ll take the average so not many people are happy with their salary I’m just finding out I mean this is a real survey this is real data so I mean it’s uh pretty interesting let’s go to the title let’s go to happy with or maybe it’s happiness happiness with salary maybe that’s what we should make it and I’m going to change that over here as well I think it sounds better some of this I’ve already planned out some I haven’t this is not something I’ve planned out so uh so we’re going to say happiness with work life balance happiness with salary really interesting um we may go back and tweak these just a little bit in the future but the very last visualization that we’re going to do is male versus female kind of got to have that in there um I don’t typically like py charts and donut charts but you know I’m feeling I’m just feeling it so let’s try it um and we will do see let’s make this larger so we have male female and what do we want to look at like what do we want to measure so we have male versus female we can measure anything um but maybe what we’ll do is the average salary again I mean we’ve kind of only looked at salary once in this one right here um and a little bit of like how happy they are but we’ll look at the average salary between males and females and then we’ll look at not the current age Oops I meant average salary and then we’ll look at the average and it looks like the average salary is actually really close versus males versus females 55 for female versus 53 for male so actually the females are a little bit higher congratulations so they’re just a little bit higher in terms of pay so now we need to start organizing all of this cleaning it up making it look a lot better than it does right now it looks great uh you know but we can do a lot more with this so I’m gonna I’m we’re we’re going to keep these or all these kind of over on this left hand side I’m going to put this I want this up here we also need to change that title I want this up here um and again we’re GNA kind of change the theme as we go I just want to format it right we have it just like this let’s change the title of this let’s go to title and we’re going to say country of survey takers uh I’m not the the survey takers I’m not really stuck on that if you find something better you think of something better I would go with that but um you know it definitely doesn’t look bad and where did this where did my other visualization go there it goes um I think this one I want to make kind of more tall um so I might move it this this way jeez this is such a I hate I hate having a lot of visualizations on here it just really is annoying to me so what we’re going to do I think we’re gonna step this to the side put this to the side as well I want to make it to where it’s just okay I didn’t want it to cut off we’ll do that might make these make these a little bigger actually so I want it to kind of match the size like right there match this perfect this one I kind of want to bring over here and bring it down a little bit maybe something like this maybe I’m not sure I’m not I’m not sold on that um I added a few different visualizations that I didn’t have in my original so now I’m kind of having to do this on the fly so um I might fast forward some of the parts where I’m like really thinking about it or taking too much time on it but I’m going to bring this down a little bit actually because I don’t like how close that is to um the the text above it but one thing we do need to do I’m going to put this up kind of like this I think that looks fine I think I’m going to put this at the very bottom so let’s make some room for it all right just like that stretch it to the side and we’ll lower it and I think we’ll keep that as is kind of like this um okay there’s a lot going on in here and there are some things I’m just noticing as we’re walking through this that I kind of missed um like I need to change some titles and stuff like that so let me go ahead and change some of those things so we’re going to do title do average salary by gender or by sex do like that average salary by sex I also don’t like that it’s in the middle um I don’t like that it’s on the outside I want them on the inside for this so let’s go to the details let’s go to inside and see if that looks any better oh that looks terrible um let me see if I can change that maybe I don’t no I definitely want it um I guess we’ll do outside I you can’t even see the information oh the decimal is crazy long um let me go and see if I can change that decimal to just like a whole number or like 1.1 uh because that’s a problem so maybe I need to go over here to the value all right so I think I want to change this one it’s just not working out exactly how I wanted and you guys know if I make mistakes I’m going to keep it in here so you guys can see it I I hoped that this was going to turn out better but it didn’t um one that I do want to add because this is kind of a a breakdown and a nice visualization I want to add this difficulty piece so I want to add this how difficult was it for you to break into data science let’s get rid of these and I want to click on this really quickly see what it gives us um values okay so now this shows us percentages um of how easy it was again it’s neither easy nor difficult difficult easy very difficult very easy these numbers make absolutely no sense we need to kind of order them a little better so I’m going to come over here to slices we have our colors over here we want very difficult to be like the most difficult um so we’re going to make that red and then we want difficult to be maybe like an orange let’s see if we can find an orange there we have an orange this does not look red enough there we go oh no no no very difficult is red difficult is orange we have neither easy nor difficult and that’s kind of a neutral um let’s see if we have something neutral in here kind of like this yellow I don’t know let’s try it out then we have easy and very easy and these will be like our Blues so I’m going to keep that um I’m going to keep that kind of like a dark blueish and then our blue for super easy is just going to be like really blue U and that doesn’t look bad the I mean look I’m I’m not a color person I I’m not great with colors and we’re going to kind of organize this in just a little bit but this looks better to me um but we need to change up some stuff as well like the title need to do difficulty to break into Data there we go and we’re also going to change this title right here we’re just say difficulty difficulty difficulty this looks better to me um again not perfect and there’s a thousand different things you could have done but that’s just what we’re going to do I need to go through here and see what I need to change so right off the bat I can see I need to change this um to let’s see right here I’m going to rename this job title just like we did in this one right here uh count of Voters that’s fine programing language breaking into difficulty happiness happiness average count okay okay so what we have here is very close to a finished product now it’s not 100% complete I mean I I do want to make it look a little nicer rather than just the typical white so what we’re going to do we’re going to go up here we’ll go to uh what is it View and we have all these different filters and we’re just kind of play around with it see if we can find something that we like um this doesn’t look too bad it’s uh not really my style u we can do this one Frontier this is pretty neat I kind of am digging this we might come back to it I like the natural tones I don’t know why I said tones like that but I did um this one’s not bad but I don’t I don’t that’s not that’s not my I don’t like how dark that is um and so maybe it’s like you know uh we change like the background color of all of these as well as match it with um match it with something else whatever you want genuinely you customize this however you want I kind of like this one it’s kind of groovy man and um it’s not perfect by any means but what we can do and we can customize this current theme we can come in here customize this theme however we’d like I personally don’t want color five which is the data analyst color I don’t like it to I don’t want to go out go and change it because I don’t like it but I don’t really like that color per se you know I might want to choose a different color um but it has to be like this muted like it has a style to it so you can come in here and you can customize this and make it however you’d like and and really mess around with it play around with it for me uh I’m just going to keep it how it is because I don’t really want to mess with it and break it or anything like that so um let me just that up just a tiny bit so this is it this is the project I hope that it was helpful um I am not joking when I say that I’m because I’m gonna do a different project I’m gonna go really in depth in another project it’s probably GNA be like a two hour project it’s gonna be crazy long um well for a YouTube video but I can see doing a thousand different things with this data creating a really great dashboard really cleaning cleaning the data which is a large part of of actually doing this and we didn’t do much data cleaning at all there’s just so much you can do with this and so really dig into this see what you like see what you don’t like see what you want to clean what you don’t want to clean you could put it in SQL you could put it in um Excel and just and just standardize the data to make it a lot more usable do whatever you want with it I mean I I took this survey for you guys that we could use it so go out and use it and make the best dashboard that you can POS possibly do so I hope that this was helpful I hope that you enjoyed this thank you so much for watching this video If you like this thank you so much for watching if you like this video be sure to like And subscribe below and I’ll see you in the next video [Music]

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!