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.
Output: (DAY WISE)

Output: (MONTH WISE) inorder to get month wise output delete the day column under the columns
option.
Q2: Top 3 Month Names and Gross Sales
Step 1: Load sheet 1 data of financial sample excel
sheet.
Step 2: Click on data option (Left side of the canvas)

Step 3: Click on New table on tool bar.

Step 4: Insert the syntax given below and click enter.

Top 3 Gross sales month wise=
TOPN(3,'Sheet1','Sheet1'[Gross sales])
This gives the top 3 gross sales data against the segment, country,
product, date etc
or
Step1: Select the Table from visualizations.
Step2: Drag and drop Month Name in the table.
Step3: Drag and drop Gross sales in the table.

Step4: In filters select Filter Type as “Top N” for Month

Name.
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.
Step1: Choose Matrix from the visualization tools. (As
the question deals with multiple dimensions like
country, product and sales we select matrix. All other
visualizations displays only product and sales)
Step2: Drag Country to rows
Step3: Drag Product to columns
Step4: Drag Sales to values.
Q4: Sales price by Date and Country.
Step 1: Select Clustered column chart.
Step 2: Drag and drop Country onto the X-axis field.
Step 3: Drag and drop sales in Y-axis field.
Step 4: Drag and drop Date Hierarchy into Legend field
and using drop down arrow set the filter to date instead
of year.
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 columns
Step 4: Drag and drop Manufacturing price in values

Step1: Select Clustered Column chart

Step 2: Drag and drop Product in X - Axis
Step 3: Drag and drop Manufacturing Price in Y-Axis
Step 4: Drag and drop Year in Legend

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.
Step 5: Copy the visual and then click on clustered
column chart.
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
Step 5: Copy the visual and then click on clustered
column chart.
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.
Q10: COGS and Profit find Correlation through
Scatter Plot.
Step1: Select Scatter plot.
Step2: Drag and drop COGS onto Values
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.
Step7: Based on the obtained we can say that COGS
and Profit are positively correlated.
11 – 20
Global Super Store Data
Import Global Super Store Data from your files.
11. Percentage of total sales for each ‘Ship mode’.

Step1: Click on data

Step2: Click on New Measure on Table Tool Bar

Step3: Enter “TOTAL SALES = SUM(Orders[Sales])” and then

click on enter key.
Step4: A new field gets added “Total Sales”
Step5: Go to “Report view” just above the “Data view”
Step6: Then select the table from Visualizations.
Step7: Select “Ship Mode” from the fields.
Step8: Select “Total Sales” from the field.
Step9: Below the visualizations there is columns field in that
right click on the “Total Sales”.
Step10: Select the option show values as
Step11: Click on Percent Of Grand Total.
Step12: Select the total sales and, now we can view % of total
sales as well as the Total Sales.
Step13: Select pie chart from visualizations.
Step14: Select Ship Mode and Total Sales.
Q12. Category with total sales higher than
4744000
Step1: Select Matrix.
Step2: Drag and drop Category on to the matrix.
Step3: Drag and Drop total Sales on to the matrix.

Step4: Select the visual and select Sum of Sales in the

filters.
Step5: In this filter Select the filter option as Is Greater
than from show items when the value
Step6: Enter the value 4744000 and click on Apply
filter.
Step7: Filter is applied and we get Technology has
greater value than 474400.
Q13: Insert a pivot table shipping cost by
category, region and segment.
Step1: Select Matrix
Step2: Drag and Drop Category and Shipping cost
Step3: Select the bar graph.
Step 4: Select Matrix
Step5: Drag and Drop Region and Shipping cost
Step6: Select the bar graph.
Step7: Select Matrix
Step8: Drag and Drop Segment and Shipping cost
Step9: Select the bar graph.

In a new sheet
Step 1: Select Matrix
Step2: Drag and Drop Region, Segment and Category
in Rows
Step3: Drag and Drop Shipping Cost in Columns
Step4: Drag and Drop Category in Legend

Step5: Create a Decomposition Tree :

Step6: Select Decomposition Tree
Step7: Drag and drop Shipping cost
Step8: Drag and drop Segment
Step9. Drag and drop Category
Step10: Drag and drop Region
Q14: Discount by ship mode
Step1: Select Matrix
Step2: Drag and drop Discount and then ship Mode
Step3: Format the table as required
Step4: Select Doughnut Chart
Step5: Drag and drop Discount and then Ship mode
Step6 : Format as required.
Q15: Quantity by segment and category
Step1: Select Matrix
Step2; Drag and Drop Segment
Step3: Drag and Drop Category and then quantity.
Step4: Format the table as required
Step5: Select a proper chart to visualise
Step6: Drag and drop the fields
Q16: Make a decomposition table of sales.
Step1: Select the decomposition table.
Step2: Drag and Drop Sales
Step3: Drag and drop Segment
Step4: Select high value in the add on button.
Step5: Drag and drop Category
Step6: Drag and drop Sub-category
Step7: Drag and drop region
Step8: Drag and drop country
Step9: Drag and drop city
Q17: Quantity over ship date. ( Insert line chart)
Step1: Select the line chart
Step 2: Drag and drop ship date
Step3: Drag and drop quantity.
Q18: Country and sales ______ country /profit /
Quantity. Provide the maps.

Step1: Select map.

Step2: Drag and drop country

Step3: Drag and drop sales

Step4: Drag and drop profit

[Link] mode & profit (doughnut chart).
Step1: Select Doughnut chart
Step2: Drag and drop Ship Mode
Step3: Drag and Drop Profit
[Link] shows a repeating pattern over
ship date.
Step1: Select line chart
Step2: Drag and drop ship date.
Step3: Drag and drop Discount.
Step4: Under X-Axis delete quarter and day options
for Ship Date.