ADVANCE DATA VISUALIZATION
PRACTICAL QUESTION BANK NOTES
Questions (1-10)
Download and install PowerBI on your desktop.
Get
Started.
Click on
Import data from Excel.
Import
Financial Sample from your files.
Q1: Percentage of total ‘Gross Sales’ for each Month.
Step1: Click on Modelling on tool bar.
Click on Quick measure
A dialogue box opens.
Under
calculation, Select Year-to-date Total
Under
Fields, click on the arrow and expand the fields. Select
gross sales, drag, and drop in Base value box.
Select
date, drag, and drop in date box.
Click Ok
Gross
Sales YTD gets added in the fields.
Step2:
Select table from visualization.
Step3:
Drag Date from fields and drop on table.
Step4:
Drag and drop Gross Sales YTD on the table.
Step5:
Repeat the same procedure – Drag and drop Gross Sales YTD on the table.
Step6:
Under values – Gross sales YTD – click on the down arrow. Select show values as – percent of grand total.
Q2:
Top 3 Month Name and Gross Sales
Step 1:
Go to data (Left side of the canvas)
Step
2: Go to new table on tool bar.
Step 3:
Insert the syntax given below and click enter.
Top
3 sales month wise DAX = TOPN(3,'Financial
Sample(1)', 'Financial Sample(1)'[ Sales])
This
gives the top 3 sales data against the segment, country, product, and date.
Or
Step1: Select the Table from
visualizations.
Step2: Drag and drop Month Name in the
table.4
Step3: Drag and drop Gross sales in
the table.
Step4: In filters click the down arrow
button under Month Name.
Step5: Select Filter Type as “Top N”.
Step6: In “Show Items” type 3 as we
want top 3 month name.
Step7: In “by value” drag and drop
Gross Sales.
Step8: Click on Apply Filter.
Step9: Select donut chart from
visualizations.
Step10: Drag and drop Gross Sales and
Month Name
Q3:
Sales by Country and Product. (Insert pivot table)
Step1:
Choose Matrix from the visualization tools.
Step2:
Drag Country to rows
Step3:
Drag Product to columns
Step4:
Drag Sales to values.
Include
labelling as required.
Q4: Sales price by Date and Country.
(Insert pivot chart)
Step 1:
Select Clustered column chart.
Step 2:
Drag and drop Country onto the chart
Step 3:
Drag and drop Date Hierarchy.
Step 4:
Drag and drop sales in values
Q5:
Manufacturing price by Product and Year.
To insert
the pivot table
Step1:
Click on Matrix table
Step 2:
Drag and drop product in rows
Step 3:
Drag and drop year in rows
Step 4: Drag
and drop Manufacturing price in values
Step1:
Select Clustered Column chart
Step 2:
Drag and drop Product in Axis
Step 3:
Drag and drop Date in Legend
Step 4:
Drag and drop Manufacturing Price in values and click enter.
Include
labelling as required.
Q6:
Segment and Units sold ( Insert Pivot Chart)
Step1:
Select the matrix.
Step2: Drag
and Drop the segment in the table.
Step3:Drag
and drop Units sold in the table.
Step4:
Format as required.
Q7:
Discount and Manufacturing Price
Step1:
Select Matrix table.
Step2:
Drag and Drop Products.
Step3:
Drag discounts on the table
Step4:
Drag and drop Manufacturing price
Q8:
Year and Discounts. (Insert doughnut chart)
Step1:
Select a Doughnut Chart ( or double click)
Step2:
Drag and drop year on to the chart.
Step3:
Drag and drop discounts on to the chart.
Q9:
Frequency of Manufacturing price
Step1:
Select the Bar chart.
Step2:
Drag and Drop date Hierarchy.
Step3: Drag and drop Manufacturing Price.
Step4:
In this graph we can drill down and drill up the data.
Q10: Cogs and Profit find correlation
through Scatter Plot.
Step1: Select
Scatter plot.
Step2:
Drag and drop COGS onto Details ( below fields)
Step3:
Drag and drop COGS onto X-axis
Step
4: Drag and drop Profit onto Legend.
Step5;
Drag and Drop Profit onto Y-axis.
Step6:
Format the Image as required.
Extra:
Create a dash board of sales with KPIs and also establish the relational data
models.
Step1:
Select the matrix table.
Step 2:
Drag and drop Sales on to the table.
Step3:
Drag and Drop Manufacturing Price, Discounts, Units sold, Sale price, COGS, and
Product on to the table.
Step4:
Click on the KPI’s.
The
average sales increases by 737.3K when - COGS goes up 1801910.86
The
average sales increases by 688.7k when – Discounts goes up 324012.54
The
average sales increases by 611.7k when – sales price goes up 1323.35
The
average sales increases by 57.37k when – Units sold goes up by 20604.43
Comments
Post a Comment