How Analysts Use Power BI to Turn Dashboards, DAX, and Messy Data into Action

Data runs the world.
From a set of raw data, an analyst is supposed to make sense of it.
One of the key tools to derive insights from large data sets is Power BI.
Power BI is preferred since it can easily handle different data formats.
The analyst has to clean the data through actionable steps on Power BI, like -

• Removing duplicates from various rows/ columns
• Removing pseudo blanks; i.e., unknown values, errors and nulls
• Find and replace values
• Text cleaning that involves inputting capitalizations, lowercases, proper names and uppercases

These actions are conducted on Power Query Editor. This is done through the following steps:

REMOVING BLANK COLUMN/ ROWS
Select the column or row that needs to be worked on:
- Right-click on the column/ row
- On the pop-up options displayed, select the remove blank option
- This can also be used to remove duplicates and pseudo-blanks

Power BI uses data analysis expressions (DAX) functions to create tables, measures and columns.
These tools are also important for solving simple and complex calculations, including mean, sum, maximum values per item (MAX), minimum values per item (MIN) and geometric mean.
Power BI displays its calculated values through either a new column or a new measure.
A new column creates a column which displays a set of values from the data being used, then calculates the value and displays it on the right tab under the Power BI title chart.
A new measure requires the help of the card tool on Power BI to visualize the expected outcome of a certain data type item.

Depending on the item that you seek to find on Power BI, you will be able to find it.
The following example best expresses how this occurs on Power BI:

AVERAGE Function

Average Planted Area = AVERAGE('Kenya_Crops_Power BI DATASET'[Planted Area (Acres)]) 
when calculating the average planted area

MAX Function

To get the Highest Revenue of a given dataset, this is the expression that is input into the Power BI.

Highest Revenue = MAX('Kenya_Crops_Power BI DATASET'[Revenue (KES)]) 

The calculated measure values are then displayed through the new measure option as follows:

The above illustrations can be replicated when finding the MIN values, geometric mean, sum, and averages.

Power BI is also great at finding logical relationships with the help of logical functions.
Most of these logical functions are developed under the new column in Power BI.
To best expound on these functions; we are going to adopt two examples:

Yield Category

When given a Kenya crops dataset example and told to categorize the yield category into high, medium or low.
This is how to go about it on Power BI:

Yield Category Classification = IF('Kenya_Crops_Power BI DATASET'[Yield (Kg)]>3000, "High",IF('Kenya_Crops_Power BI DATASET'[Yield (Kg)]>=1000,"Medium","Low"))

This is how Power BI will work on the data to display the following information:

Planting Date in Quarters

When given a Kenya crops dataset example and told to categorize the planting date into quarters - quarter 1, 2, 3 and 4.
This is how to go about it on Power BI:

Planting Quarter = IF(MONTH('Kenya_Crops_Power BI DATASET'[Planting Date])<=3, "Quarter 1", IF(MONTH('Kenya_Crops_Power BI DATASET'[Planting Date])<=6,"Quarter 2", IF(MONTH('Kenya_Crops_Power BI DATASET'[Planting Date]<=9), "Quarter 3", "Quarter 4")))

The display on Power BI as the result of the logical expression above will be as follows:

Graphs used to Document the Data Items Results

The analysis of data is not complete through illustrations of the data displayed in the form of different graphs.
This makes it easier to develop insights that will be relevant to assist the decision makers in arriving at impactful decisions on the enterprise.
When you are asked to develop a chart that will display the top 10 farmers in our illustration data - Kenya_ Crops_ Dataset.
This is how to go about it.

• Select the items to be placed on the x-axis and y-axis.

In this case, the x-axis represents the sum of revenue, and the y-axis represents the farmer's name

• On the visualization tab on the right-hand side of Power BI, select the appropriate chart type.

In this case, the chart type was a clustered bar chart.

• The other key item that needs you to be keen on is the application of filters to narrow down the data from all the farmers to the top 10 farmers to be displayed on the chart.

The filter chart is applied after selecting the chart as follows:

• Click on the chart
• On the right side of your Power BI, select the filter tab.
• Keep your eyes on the filter type - select the Top N
• Thereafter, under the show items, select Top and input the number you want, in this case 10.

The graph displayed is as shown below:

Several other charts can be developed using the above steps.
I will look into the donut chart, Q & A chart, filled map and combo chart that I will use as my final display on the dashboard.

Donut Chart

• This is used to display data analysis in duo combinations of interrelated data items, for example, revenue and crop variety.
• It is used when the display is not "overcrowded"
• This is important to help in easier analysis.

A display of this chart is as follows:

Q & A Chart

This is a visualization that helps the analyst to place a platform where anyone can ask questions to the dashboard and get replies on the data under study.
The chart is displayed as follows:

Filled Map

• Uses the global positioning system (GPS) to pinpoint where your case study data is from around the globe.
  The only anomaly with this display item is that it uses well - known items names when displaying on the map.
  An illustration is when analysing county information, our map pointed out Meru in Tanzania instead of Meru, a county in Kenya
  This can be corrected by using these steps:

• Select the county item on the data tab item under Power BI in the Kenya _ Crops _ Dataset

• Go to the column tools on the Power BI ribbon

• Under data category, select State/Province

• It rectifies the issue with GPS picking the well-known landmarks around the globe.
  This displays the steps above:

The filled map is displayed as follows:

Combo Chart

• It displays two or more variables that are under analysis.
• This could be a revenue and profit analysis by county.

The chart is displayed as follows:

Dashboards on Power BI

A high-level view of specific KPIs from one or more reports is offered by dashboards.
Reports are added to an existing or new dashboard after being pinned to a live site.

Importances of Dashboards in Power BI

• Enhanced Data Storytelling
  Dashboards facilitate the communication of insights by assisting in the explanation of the "what," "when," "why," and "how" underlying data using interactive tiles and, in certain situations, data storytelling.

• **Customization and Personalization*
  Dashboards can be tailored to provide metrics that are significant to particular departments or jobs, guaranteeing that users get the most important data.

• Useful Information from Q&A
  Data discovery is facilitated by the integrated Q&A function, which enables users to pose queries in natural language and get prompt visual responses.

An example of a dashboard is as follows, developed from the set of charts shared above.

It has been an amazing learning experience on Power BI.
This is an important tool for anyone seeking to understand data analysis. This is my input in the learning curve towards mastering data analysis.
Your input in the form of comments, clarifications and guidance will be important towards bettering my journey towards being great in this field.
Happy learning, all!📌