PROGRAM 9: BUILDING DASH BOARD
Analysis of revenue in sales dataset:
i) Create a choropleth map (fill the map) to spot the special trends to
show the state which has the highest revenue.
ii) Create a line chart to show the revenue based on the month of the year.
iii) Create a bin of size 10 for the age measure to create a new
dimension to show the revenue.
iv) Create a donut chart view to show the percentage of revenue per
region by creating zero access in the calculated field.
v) Create a butterfly chart by reversing the bar chart to compare female
& male revenue based on product category.
vi) Create a calculated field to show the average revenue per state &
display profitable & non-profitable state.
vii) Build a dashboard.
Solution:
Step1: Upload the revenue dataset
Step2: In the power query editor as part of transformation remove the unnecessary
columns (Remove the last null column)
Question 1: Create a choropleth map (fill the map) to spot the special
trends to show the state which has the highest revenue.
Step1: Select the "Map" visualization from the Visualizations
pane.(filled map) Step2: Set Up the Map:
Drag the state field to the "Location" field well.
Drag the revenue field to the "Size" or "Values" field well.
Step3: Customize: In the "Format" pane, adjust settings such as color, size,
and tooltips to enhance readability. You can use color gradients to indicate
different revenue levels, helping to spot trends.
Dept. of ISE 66
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Question 2: Create a line chart to show the revenue based on the month
of the year.
Step1:Add a Line Chart: Select the "Line chart" visualization from the
Visualizations pane.
Step2: Configure the Chart:
Drag the month field to the "Axis" field well.
Drag the revenue field to the "Values" field well.
Step3: Format: In the "Format" pane, you can customize the line color, axis
titles, and other aspects to clearly present the revenue trend throughout
the year
.
Question 3: Create a bin of size 10 for the age measure to create a new
dimension to show the revenue.
Step1: Create Bins for age
Go to the "Data" view and select the age field.
Right-click on the age field and choose "New group".
In the "Group" window, select "Bin" and set the bin size to 10.
Step2: Add to Visualization:
Create a new visualization (e.g., bar chart or column chart). Here we used
Stacked column chart.
Drag the new age bins field to the "X Axis" and the revenue field to the "Y axis”.
Dept. of ISE 67
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Question 4: Create a donut chart view to show the percentage of revenue
per region by creating zero access in the calculated field.
Step1: Add a Donut Chart: Select the "Donut chart" visualization from the
Visualizations pane.
Step2:Set Up the Chart:
Drag the region field to the "Legend" field well.
Drag the revenue field to the "Values" field well.
Step3: Create Zero Access:
Go to the "Format" pane, select "Detail labels", and set the "Label
position" to "Inside" to create a zero access effect.
Adjust the "Detail" and "Percentage" settings as needed.
Dept. of ISE 68
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Note: The "zero access effect" is a visual design technique often used in data
visualizations to emphasize or clearly show zero values or the absence of
certain data. This effect is particularly useful in charts where you want to
highlight how values are distributed relative to zero, or where zero plays a
significant role in the interpretation of the data.
Donut Charts:
In a donut chart, the zero access effect can be used to enhance readability by
placing labels or markers at the center of the chart or using a specific design
to show where there is no data.
For example, if one segment of a donut chart represents zero revenue, you
might design the chart so that this segment is clearly visible or highlighted
to indicate no revenue.
Question 5: Create a butterfly chart by reversing the bar chart to compare
female & male revenue based on product category.
Step1: Create a New Measure Total
Revenue = sum(Sales Table[Revenue])
Dept. of ISE 69
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Method-1: By using stacked column chart
Method 2:
Step 1:
Add Two Bar Charts:
o Create two separate bar charts from the "Visualizations" pane.
Step 2:
Configure the First Bar Chart (e.g., Female
Revenue): Drag Product Category to the "Axis" field.
Drag Total Revenue to the "Values" field.
Apply a Filter:
In the "Filters" pane, add a filter to show only Female revenue. You can
drag Gender to the "Filters" pane and set the filter to include only Female.
Step 3:
Configure the Second Bar Chart (e.g., Male Revenue):
Drag Product Category to the "Axis"
field. Drag Total Revenue to the
"Values" field. Apply a Filter:
In the "Filters" pane, add a filter to show only Male revenue. You can
drag Gender to the "Filters" pane and set the filter to include only Male.
Step 4:
Reversing the Bars:
To create the butterfly effect, you need to reverse one of the bar charts.
This involves adjusting the direction of the bars so that they face
opposite directions from the center. o Reverse the Bars:
For one of the charts (e.g., Male revenue), you will need to use a calculated
column or measure to make the bars extend in the opposite direction. In
Power BI, this can be achieved by adjusting the data in the chart's settings or
using custom visualizations if necessary.
Dept. of ISE 70
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Add Titles and Labels:
Add clear titles and labels to each chart to indicate what data they
represent (e.g., "Female Revenue" and "Male Revenue").
Customize the chart's appearance to enhance readability.
Dept. of ISE 71
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Question 6: Create a calculated field to show the average revenue per
state & display profitable & non-profitable state.
Step1: Create a New Measure
Go to the Modeling tab and select "New Measure".
Create the Average Revenue Measure:
Enter the following DAX formula to calculate the average revenue per state:
Average
Revenue
Per State =
AVERAGEX(
VALUES(SalesTable[State]),
CALCULATE(SUM(SalesTable[Revenue]))
)
Step2: Create a Calculated Column to Categorize States
Next, create a calculated column to classify states as profitable or non-
profitable based on the average revenue.
1. Go to the Modeling tab and select "New Column".
2. Create the Profitability Column:
Enter the following DAX formula to create a column that categorizes states
as profitable or non-profitable:
ProfitabilityStatus =
IF(SalesTable[AverageRevenuePerState] > 1000,
"Profitable"
, "Non-
Profitable"
)
Step 3: Display the Results
1. Add a Table and select state, Average Revenue State and ProfitablitiyStatus.
Dept. of ISE 72
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Extra: To get the Total value or single value
In Power BI, a Card visualization is used to display a single, important piece
of data, such as a key metric or a number. It is commonly used to show
aggregate values like:
Total Sales
Average Profit
Total Units Sold
Number of Customers
The Card provides a clean and simple way to highlight critical metrics that
are important for decision-making. It's ideal for dashboard views where
quick insights are needed.
To show the total revenue
Drag the Revenue field into the field well
Dept. of ISE 73
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
To Add filter or Slicer
Filter Data: Slicers filter data across multiple charts and visuals in a report.
For example, selecting a specific region or product category in a slicer can
update all connected visuals to reflect data only for that selection.
Steps : 1. Select slicer from the Visual pane
2. Drag the Country field into the field well
Question 7:Build a dashboard.
Dept. of ISE 74
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])