Power BI - Assignment 5 | LTD Sales Analysis

#dataanalytics #businessintelligence #powerqueryeditor #powerbi

Sudhanshu Rane Apr 08 2021 · 2 min read
Share this

Objective:

LTD Sales data which is in an Excel sheet needs to be transformed into something more presentable using Power BI. Tasks to be performed:
1. Need to showcase the Revenue as per the State.
2. Create a new column named 'Sales Range' and categorize the sales value using Conditional columns.
3. Create a new column named 'Product Sub-category' and categorize the products using Conditional columns.
We have to transform data using Query Editor especially working with the Add column & conditional column function.

Analytics:

Open Power BI application. Load the required Excel sheet from the 'Home' option, and then click on transform data as shown below.

1. Showcasing the Revenue as per the State:
In power query editor, click on 'Add column' --> 'Custom Coulmn' and then name the new column as 'Revenue' and use Custom column formula as =[Quantity]*[Unit Price] and then click on OK.
We can also make a formula by using the Available column option and then by using the Insert button as shown below.

Once the revenue column is generated, we will click on 'Close & Apply' as shown below.

In order to showcase the revenue generated as per state, we will use a 'Stacked Bar Chart' Visualization in the report view as shown below

2. Creating a new column named 'Sales Range' and categorizing the sales value using Conditional columns:
In order to create a new sales column based n certain conditions, we will click on 'Add Column''--> 'Conditional Column' and use the conditions as shown below in the image and then click on OK. A new conditional column would be generated.

3. Creating a new column named 'Product Sub-category' and categorizing the products using Conditional columns:
In order to create a new product sub-category column based on certain conditions, we will click on 'Add Column''--> 'Conditional Column' and use the conditions as shown below in the image and then click on OK. A new conditional column would be generated.

Hence, We have transformed the data using Query Editor especially working with the Add column & conditional column function. Now we will see how our final dashboard looks and some key insights of this data set.

Final Dashboard:

Key Insights:

1. The state of New York has generated the highest Revenue whereas the state of Maine has generated the lowest Revenue.
2. Maximum revenue is generated by Sales Range of above 25000.
3. As far as Product category is concerned, maximum revenue is generated by Office Supplies which is about 39%.
4. If the products are categorized by 'Office Infra' & 'Non-Office infra', the Non-Office infra has generated maximum revenue of about 65% out of the total revenue generated.

****************************************************************

Comments
Read next