Introduction
As a data analyst in a company most of the data you receive is always mixed up. Some missing values, wrongly formatted data and some are just weird and don't make sense. Worst part is that the data contains thousands of rows and tens of columns.
It is paramount that as a data analyst you find your way around this. That is where Power BI comes in.
The most important reason why we use Power BI is because it can be used to manipulate large datasets.
Once we load the data, this is the most common pathway most analysts use to analyze data.
Data Cleaning ---> Data Enrichment ---> Data Visualization
Data Cleaning
Once your data has been loaded into power BI we transform the data and start cleaning.
Cleaning involves: - Removing duplicates
- Removing empty cells
- Replacing some data
- Formatting columns correctly
- Data consistency checks
Data cleaning is one of the most important steps because if you skip this step, it will lead to data breaks later on, difficulties building relationships and also some arithmetic calculations may occur unnecessarily. For example a column may have numbers but you do not want to add those numbers i.e month number (nobody wants to add the month number we just want to know if it is the eighth month or not).
Data Enrichment
This is the addition of columns and deriving new columns from other existing columns to make the data easy to understand and analyze.
We can add new columns using the "new column" function and new measures using the "new measure" function shown below.
In power BI, this is where something called DAX comes in.
What is DAX?
DAX stands for data analysis expressions which is a library of functions and operators that can be combined to build formulas and expressions in Power BI.
DAX is what functions and operators are in excel, just simpler and easier to understand and visualize.
There are many DAX functions.
The most basic and common ones are these three:
1. Aggregation functions- These functions calculate a value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression. These are the most basic DAX functions and are very useful.
2. Date and time functions - These functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server.
3. Filter functions - These functions help you return specific data types, look up values in related tables, and filter by related values. Lookup functions work by using tables and relationships between them. Filtering functions let you manipulate data context to create dynamic calculations.
Below are some of the examples:
Total Sales = SUM(Sales[Amount]) - gives sum of sales in the sales column
Total Orders = COUNTROWS(Sales) - give total number of rows in the sales column(assuming every cell = 1 sale)
Avg Order Value = DIVIDE([Total Sales], [Total Orders]) divide total sales by total orders
There are filter functions which control rows of data that are included when a calculation is performed.
We use CALCULATE to filter.
Kiambu Planted Area = CALCULATE(SUM('Kenya_Crops_Power BI DATASET'[Planted Area (Acres)]), 'Kenya_Crops_Power BI DATASET'[County]= "Kiambu") This statement means: you are calculating the sum of planted area in acres in Kiambu county. It just adds the planted areas for Kiambu County only.
If you are trying to filter multiple things then
Total Profit Potatoes Meru = CALCULATE(SUM('Kenya_Crops_Power BI DATASET'[Profit (KES)]),'Kenya_Crops_Power BI DATASET'[County]="Meru", 'Kenya_Crops_Power BI DATASET'[Crop Type]="Potatoes", 'Kenya_Crops_Power BI DATASET'[Market Price (KES/Kg)]>100)
All the above require you to select the "new measure function".
However, some will require you to make new tables in order to see specific data sets.
In such cases we use the FILTER function
Select new table on ribbon --> Then write a function
Meru Data = FILTER('Kenya_Crops_Power BI DATASET','Kenya_Crops_Power BI DATASET'[County]="Meru"
For this a new table with be created showing only data from Meru County as shown below
If you want to apply multiple filters we use '&&' as shown below:
Meru Data = FILTER('Kenya_Crops_Power BI DATASET','Kenya_Crops_Power BI DATASET'[County]="Meru" && 'Kenya_Crops_Power BI DATASET'[Crop Type] = "Cassava")
This filters for both Meru and Cassava as shown below.
Data enrichment just involves adding new things that will make the data more insightful and useful.
Data Visualization
After all the above is done, we need to communicate the important findings to key stakeholders.
This is where visualization comes in. Data visualization is the graphical representation of information and data. By using visual elements like charts, graphs, and maps, data visualization tools provide an accessible way to see and understand trends, outliers, and patterns in data.
This is what the dashboard contains.
The dashboard
A dashboard is a way of displaying various types of visual data in one place. Usually, a dashboard is intended to convey different, but related information in an easy-to-digest form. And oftentimes, this includes things like key performance indicators (KPI)s or other important business metrics that stakeholders need to see and understand at a glance.
There are important things that need to be observed in order to make a comprehensive dashboard. Below is the checklist.
- Ensure main message is understood in a few seconds
- Every visual must have a clear purpose.
- It must be well organized and easy to understand data, most important KPIs top left, interactivity, supports decision making.
- Make it interactive using slicers.
- Use a single theme(avoid using too many colours).
Below is a sample of dashboard note how it is simple and only the key metrics have been captured.
Conclusion
Data analysts must always convert inconsistent and incomplete data into a useful form and visualize it in a way that non-analysts can understand. It is not only about how much you can clean and enrich data but also what insights can you communicate that significantly influence decisions?
Top comments (0)