ANNAMALAI UNIVERSITY

FACULTY OF ENGINEERING AND TECHNOLOGY

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

B.E. Computer Science and Engineering (Data Science)
V – Semester

DSCP509 – DATA VISUALISATION LAB

Staff In-Charge
Dr. L. R. Sudha, Associate Professor
 CONTENTS

Ex. No. Date Name of the Exercise Page Marks Signature

No.
USING PYTHON LIBARIES

1 Generating charts using 1

matplotlib

2 Generating plots using seaborn 9

3 Generating parallel coordinates 16

using plotly

4 Drawing treemap using squarify 18

USING TABLEAU

5 Connecting with various data 20

sources

6 Building Views 25

7 Applying Filters 28

8 Working with Functions 36

9 Working with table Calculations 41

10 Creating sets and groups 45

USING POWER BI

11 Generating Charts 54

12 Creating Tables and Matrix Visuals 59

13 Working with DAX Queries 63

14 Creating Storyboard 68

Average Mark:
 Generating charts using Matplotlib
Ex. No.1
Date:

Aim:

To Generate various types of charts using Matplotlib.

i) Line Chart

Simple Line Chart:

Code:
import matplotlib.pyplot as plt
x =[3,6,8,11,13,14,17,19,21,24,33,37]
y = [7.5,12,13.2,15,17,22,24,37,34,38.5,42,47]
x2 =[3,6,8,11,13,14,17,19,21,24,33]
y2 = [50,45,33,24,21.5,19,14,13,10,6,3]
plt.plot(x,y, label='First Line')
plt.plot(x2, y2, label='Second Line')
plt.xlabel('Plot Number')
plt.ylabel('Important var')
plt.title('Interesting Graph\n2018 ')
plt.yticks([0,5,10,15,20,25,30,35,40,45,50],
['0B','5B','10B','15B','20B','25B','30B','35B','40B','45B','50B'])
plt.legend()
plt.show()
Output:

1
With Dataset:
Code:
import pandas as pd
import matplotlib.pyplot as plt
dataset= pd.read_csv("salaries.csv")
plt.plot(dataset['phd'],label='phd')
plt.plot(dataset['service'],label='service')
plt.legend()
plt.show()

Output:

ii) Bar Chart

Simple Bar Chart:
Code:
import matplotlib.pyplot as plt
Students = [2,4,6,8,10]
Courses = [4,5,3,2,3]
studs = [3,5,7,9,11]
Projects = [1,2,4,3,2]
plt.bar(Students, Courses, label="Courses", color='r')

2
 plt.bar(studs, Projects, label="Projects", color='c')
plt.xlabel('Students')
plt.ylabel('Courses/Projects')
plt.title('Students Courses and Projects Data\n 2018')
plt.legend()
plt.show()

Output:

With Dataset:
Code:
import pandas as pd
import matplotlib.pyplot as plt
dataset= pd.read_csv("salaries.csv")
plt.bar(dataset['service'],dataset['salary'],label="service/salary")
plt.legend()
plt.show()

3
 output:

Stacked Bar Chart:

Code:
import matplotlib.pyplot as plt
import numpy as np
x=['A','B','C','D']
y1=np.array([10,20,10,30])
y2=np.array([20,25,15,25])
y3=np.array([12,15,19,6])
y4=np.array([10,29,13,19])
plt.bar(x,y1,color='r')
plt.bar(x,y2,bottom=y1,color='b')
plt.bar(x,y3,bottom=y1+y2,color='y')
plt.bar(x,y4,bottom=y1+y2+y3,color='g')
plt.xlabel("Teams")
plt.ylabel("Score")
plt.legend(["Round 1","Round 2","Round 3","Round 4"])
plt.title("Score by Teams in 4 Rounds")
plt.show()

4
 output:

iii) Scatter chart

Code:

import matplotlib.pyplot as plt

import pandas as pd
dataset = pd.read_csv("salaries.csv")
plt.scatter(dataset['phd'],dataset['salary'],
label='phd/salary', color='g',marker='+', s=50 )
plt.xlabel('phd')
plt.ylabel('salary')
plt.title('phd / salary\n Spring 2018')
plt.legend()
plt.show()

5
 Output:

iv) Pie Chart

Pie Chart with Explode:
Code:
import numpy as np
import matplotlib.pyplot as plt

# Creating dataset
cars = ['AUDI', 'BMW', 'FORD',
'TESLA', 'JAGUAR', 'MERCEDES']
data = [23, 17, 35, 29, 12, 41]

# Creating explode data

explode = (0.1, 0.0, 0.2, 0.3, 0.0, 0.0)

# Creating color parameters

colors = ( "orange", "cyan", "brown",
"grey", "indigo", "beige")

6
# Wedge properties
wp = { 'linewidth' : 1, 'edgecolor' : "green" }

# Creating autocpt arguments

def func(pct, allvalues):
absolute = int(pct / 100.*np.sum(allvalues))
return "{:.1f}%\n({:d} g)".format(pct, absolute)

fig, ax = plt.subplots(figsize =(10, 7))

wedges, texts, autotexts = ax.pie(data,
autopct = lambda pct: func(pct, data),
explode = explode,
labels = cars,
shadow = True,
colors = colors,
startangle = 90,
wedgeprops = wp,
textprops = dict(color ="magenta"))

# Adding legend
ax.legend(wedges, cars,
title ="Cars",
loc ="center left",
bbox_to_anchor =(1, 0, 0.5, 1))
plt.setp(autotexts, size = 8, weight ="bold")
ax.set_title("Customizing pie chart")

plt.show()

7
 Output:

Result:
Thus, various charts are visualised using Matplotlib.

8
 Generating plots using seaborn
Ex. No.2
Date:

Aim:
To Generate various types of plots using Seaborn.

i) Box Plot

Simple Box Plot:

Code:
import seaborn as sns

import pandas as pd

dataset = pd.read_csv("salaries.csv")

sns.boxplot(x = dataset['salary'])

Output:

With Morethan One Fields:

Code:
sns.boxplot(x = 'rank', y = 'salary', data=dataset)

9
Output:

With Subplots:
Code:
sns.boxplot(x = 'rank', y = 'salary', hue='sex', data=dataset,palette=’Set3’)

Output:

10
ii) Swarm Plot

Box Plot with Swarm Plot:

Code:
sns.boxplot(x = 'rank', y = 'salary', data=dataset)
sns.swarmplot(x = 'rank', y = 'salary', data=dataset, color='0.25')

Output:

iii) Strip Plot

Code:
import seaborn as sns
sns.stripplot( x = dataset['sex'], y= dataset['salary'], data=dataset)
Output:

11
iv) Joint Plot

Simple Joint Plot:

Code:
import seaborn as sns
import pandas as pd
dataset = pd.read_csv("salaries.csv")
sns.jointplot(x = 'salary', y = 'service', data=dataset)

Output:

With Distribution:
Code:
sns.jointplot('salary', 'service', data=dataset,kind='reg')

12
Output:

With kde:
Code:

sns.jointplot('salary','service',data=dataset).plot_joint(sns.kdeplot,n_levels=6)

Output:

13
v) Heat Map

Heatmap using dataset:

Code:
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

globalWarming_df =
pd.read_csv("Who_is_responsible_for_global_warming.csv")
globalWarming_df = globalWarming_df.drop(
columns=['Country Code', 'Indicator Name', 'Indicator Code'] ,
axis=1).set_index('Country Name')
plt.figure(figsize=(16,9))

annot_kws={
'fontsize':10,
'fontstyle':'italic',
'color':"k",
'alpha':0.6,
'rotation':"vertical",
'verticalalignment':'center',
'backgroundcolor':'w'
}

sns.heatmap(globalWarming_df, annot = True, annot_kws= annot_kws)

14
Output:

Result:
Thus, various plots are visualised using Seaborn.

15
 Generating parallel coordinates using plotly
Ex. No.3
Date:

Aim:
To use plotly for plotting parallel coordinates.
Code:

import plotly.graph_objects as go

fig = go.Figure(
data= go.Parcoords(
line_color='blue',
dimensions = list([
dict(range = [1,5], constraintrange = [1,2],
label = 'A', values = [1,4]),
dict(range = [1.5,5], tickvals = [1.5,3,4.5],
label = 'B', values = [3,1.5]),
dict(range = [1,5], tickvals = [1,2,4,5],
label = 'C', values = [2,4],
ticktext = ['text 1', 'text 2', 'text 3', 'text 4']),
dict(range = [1,5],
label = 'D', values = [4,2])
]),
)
)
fig.show()

16
Output:

Result:
Parallel coordinates was generated using plotly.

17
 Drawing Treemap using squarify
Ex. No.4
Date:

Aim:
To use squarify for plotting tree map.

Tree Map

Simple Tree:
Code:
import squarify
import matplotlib.pyplot as plt
data = [300, 400, 120, 590, 600, 760]
colors = ["red", "black", "green", "violet", "yellow", "blue"]
squarify.plot(sizes=data, color=colors)
plt.axis("off")

Output:

18
Treemap with Axis:
Code:
episode_data = [1004, 720, 366, 360, 80]
anime_names = ["One Piece", "Naruto", "Bleach", "Gintama",
"Attack On Titan"]
squarify.plot(episode_data, label=anime_names, pad=2)
plt.axis("on")

Output:

Result:
Tree map was generated using squarify.

19
 Connecting with various Data Sources
Ex. No.5
Date:

Aim:

To connect tableau with following data sources.

a) Text file b) Excel file c) PDF d)Website e) MS SQL
Procedure:
1. Select data tab and connect to data
2. Click on corresponding data source.
3. Select the file.
4. Click on Open. This will connect the file into Tableau.
5. Name of the file will be displayed on the left side of the window.

To connect to a Website
1. Click on More option in the To a server data tab.
2. Select Web data Connector. This will open a Tableau Web Data Connector
Window.
3. Enter the name of Web data.

To connect to a MS SQL Server

1. Open Microsoft SQL server management studio and connect it to the
database.
2. Select Microsoft SQL Server under To a server data tab.
3. In the MS SQL server dialog box that appears in Tableau, type the server
name and click Sign In.
4. Select the database from database drop down box.

20
Sample Input Output:

1. Connecting to text file sample - Superstore.csv

(https://drive.google.com/uc?export=download&id=1xV_3j-
kn7UbHBpLd47DgY5g6obdbmQk1)

2. Connecting to Excel file

(https://drive.google.com/uc?export=download
&id=1wq60dEFV3NUPqpwXaMKtmQPnxzqZHR5F)

21
3. Connecting to PDF File with tables:

22
4. Connecting to Websites (Google Sheet)

23
5. Connecting to MS SQL Server -database

Result:
Thus the Tableau has been connected with various data sources successfully
and the output is verified.

24
 Building Views
Ex. No.6
Date:

Aim:

To build views of the connected file using dimensions and measures

automatically and customize it.
Procedure:

1. Connect the sample superstore data source Excel file to Tableau.

2. Select the sheet to view and click.
3. Click sheet1 in status bar.
4. Select the fields to view from the Data pane and drag it into columns and rows
shelves.
5. Press Show Me button on the tool bar.
6. Select the type of View.
7. Tableau automatically creates the view of the data.
8. Customize the view by selecting Custom Mark Type.

Sample Input and Output:

Input File:
Connect SuperStore.xls file

Fields:
Columns : Sales
Rows : SubCategory, Region

1. Automatic View

25
2. Change Color

3. Change Size

26
 4. Show Label

5. Show tooltip.

Result:
Thus the views have been created using dimensions and measures and
customized.

27
 Applying Filters
Ex. No.7
Date:

Aim:
To apply filters for removing certain values or range of values from a result
set.
Concept:

Filters are defined by selecting specific dimension members or a range of

measure values. All fields that are filtered will be in the Filters shelf. A filter can be
added either by selecting data in the view, dragging a field to the Filters shelf, or
turning on quick filters.
1. Creating Filters for Measures
Measures are numeric fields. So, the filter options for such fields involve choosing
values. Tableau offers the following types of filters for measures.
• Range of Values − Specifies the minimum and maximum values of the
range to include in the view.
• At Least − Includes all values that are greater than or equal to a specified
minimum value.
• At Most − Includes all values that are less than or equal to a specified
maximum value.
• Special − Helps you filter on Null values. Include only Null values, Non-null
values, or All Values.

Following worksheet shows these options.

28
2. Creating Filters for Dimensions
Dimensions are descriptive fields having values which are strings. Tableau
offers the following types of filters for dimensions.
• General Filter − allows to select specific values from a list.
• Wildcard Filter − allows to mention wildcards like cha* to filter all string
values starting with cha.
• Condition Filter − applies conditions such as sum of sales.
• Top Filter − chooses the records representing a range of top values.

Following worksheet shows these options.

Queries
Connect SuperStore.xls file and perform the following queries
Data Connection

• Connect a dataset ( Super Store.Xls File)

• Drag and drop the needed sheet of the connected dataset.
• Click on sheet 1 to open the tableau worksheet.
• All attributes in the dataset displayed on the left side. Add the dimensions
and Measures (Sub-Category, Region and Sales)
• Prepare a worksheet with some graphs or charts.

29
1. Display the ship mode and subcategory of the products having average of
profit less than 50000$.

i) Create a view with ship mode, sub category in the column shelf and profit
in the rows shelf
ii) Drag the AVG (profit) value to the filter shelf.
iii) Choose Average as the filter mode.
iv) Next, choose "At most" and give the value 50,000 to filter the rows.

2. Display profit within a period by specifying a range of dates

i) Create a view with order date in the column shelf and profit in the rows
shelf.
ii) Drag the "order date" field to the filter shelf.
iii) Choose Range of dates in the filter dialog box. Choose the dates .
iv) On clicking OK, the final view appears showing the result for the
chosen range of dates.

30
3. Select all subcategory name starting with “a”.
i) Create a view with sales in the column shelf and subcategory in the rows
shelf.
ii) Drag the sub-category field to the filters pane.
iii) All the subcategories appear next to the chart.
iv) Apply wildcard filtering using the expression “a” and click starts with.
v) This selects all subcategory name starting with “a”.

4. Show orders with an average quantity of 26 or more.

i) Create a view with Order Quantity in the column shelf and Product
Name in the rows shelf
ii) Drag the Order Quantity measure to the Filters shelf and select Average
as the aggregation.
iii) Choose At Least filter with the minimum value set to 26.

iv) When finished, click

31
5. Find the top 10 Sub-Category of products for the category called Furniture.
(Example for context filter)
i) Drag the dimension Sub-Category to the Rows shelf and the measure Sales
to the Columns Shelf. Choose the horizontal bar chart as the chart type.
ii) Drag the dimension Sub-Category to the Filters shelf.
iii) Right-click on the field Sub-Category in the filter shelf and go to the
fourth tab named Top. Choose the option by field. From the next drop-
down, choose the option Top 10 by Sales Sum.

iv) Drag the dimension Category to the filter shelf. Right-click to edit and
under the general tab choose Furniture from the list.
v) The result shows the subcategory of products from the category Furniture
which are among the top 10 subcategories across all the products.

32
6. Create a view to show only sales between $5000 and $20,000.

Select the range of values

7. Show the bottom 10 products based on sales, all products sold in the last
30 days
i) Drag Product name from Data pane to Rows shelf and Sales to columns
shelf.
ii) Right-click on Sales in column shelf and select Filter.
iii) In Filter [Sales] dialog box, select the range from 0-10 and click ok.
iv) Drag the Order data from Data pane and drop it in Filters card.
v) Select Months> Next and select the month from the list.

33
8. Show the Products that have a Time to Ship that is greater than 10 days.
i) Select this option to specify a condition based on existing fields in the data
source. Use the first two drop-down lists to select the field and aggregation
you want to base the condition on. Then select a condition operator such
as greater than, equal to, etc. Finally, type a criteria value into the text
box.
ii) By field: select Time to Ship and AVG from the first two drop-down lists.
Then select Greater ( > ) from the operator list and type10 into the text box.
iii) By Formula: select this option for more advanced filter conditions. You can
type a custom formula into the text box or open the formula editing dialog
box by clicking the button to the right of the text box.

9. Create a basic filter on the Container dimension that excludes the Small
Pack and Wrap Bag shipping containers.
Show just the top 3 of those orders in terms of sales. Exclude orders that
were shipped via Delivery Truck.

i) Drag the Container dimension to the Filters shelf to open the Filter dialog
box.
ii) Click the None button at the bottom of the list to deselect all of the
shipping containers.
iii) Then select the Exclude option in the upper right corner of the dialog box.
iv) Finally, select Small Pack and Wrap Bag.
v) When finished click OK. The view updates to only show orders that were
not shipped in a Small Pack or Wrap Bag.
vi) Now let’s refine the filter on Container by adding a limit. Right-click the
Container field on the Filters shelf and select Filter.
vii) The Filter dialog box opens. Leave the selections as they are.
Switch to the Top tab and select By Field.
viii) Select Top 3 from first two drop-down lists.
ix) Then select Sales and SUM from the remaining drop-down lists. When
finished click OK.
x) The Top formula is computed after the selections on the General tab.
Then the view shows just the top 3 of those orders in terms of sales.
xi) Now let’s add a new filter on Ship Mode to exclude orders that were shipped
via Delivery Truck.
Right-click the Delivery Truck row header and select Exclude.
xii) The Delivery Truck ship mode is removed from each region in the view.

34
10.Create a view to include only orders that were placed between August
2, 2008 and May 1, 2009.

i) Place Order Date on to the Columns shelf and select All Values as the
aggregation.
ii) Then place Profit onto the Rows shelf.
iii) Drag the Order Date field to the Filters shelf and select Range of Dates
in the Filter Field dialog box.
iv) Then click Next
v) The Filter dialog box is shown below. It displays the Order Date limits. Use
the drop- down date controls to specify a new lower limit of August 2, 2008
and an upper limit of May 1, 2009.

Result:
Thus the given queries have been executed successfully and the outputs are
verified.

35
 Working with Functions
Ex. No.8
Date:

Aim:
To work with the different categories of inbuilt functions available in Tableau
Concept:

Tableau has a number of inbuilt functions which help in creating expressions

for complex calculations.
Following are the different categories of functions.
• Number Functions
• String Functions
• Date Functions
• Logical Functions
• Aggregate Functions

Queries
1. Find the minimum sales of each category Procedure
i) Connect to the Sample -Superstore saved data source, which comes with
Tableau.
ii) Navigate to a worksheet and select Analysis > Create Calculated Field.
iii) In the calculation editor that opens, do the following.
iv) Name the calculated field Minimum Sales transaction
v) Enter the following formula:
a. MIN(Sales)
vi) When finished, click OK.

36
2. Display the order number from order ID

i) Connect to the Sample - Superstore saved data source, which comes with
Tableau.
ii) Navigate to a worksheet.
iii) From the Data pane, under Dimensions, drag Order ID to the Rows shelf.

[Notice that every order ID contains values for country (CA and US, for
example), year (2011), and order number (100006). For this example, you
will create a calculation to pull only the order number from the field.]

iv) Select Analysis > Create Calculated Field.

v) In the calculation editor that opens, do the following:
o Name the calculated field Order ID Numbers.
o Enter the following formula:

RIGHT([Order ID], 6)
This formula takes the specified digits (6) from the right of the string
and pulls them into a new field.

Therefore, RIGHT('CA-2011-100006' , 6) = '100006'.

o When finished, click OK.

The new calculated field appears under Dimensions in the Data pane.
vi) From the Data pane, drag Order ID Numbers to the Rows shelf. Place it to
the right of Order ID.

37
3. Display the year and month of order date
i) Connect to the Sample-Superstore saved data source, which comes with
Tableau.
ii) Navigate to a worksheet.
iii) From the Data pane, under Dimensions, drag Order Date to the Rows shelf.
iv) On the Rows shelf, click the plus icon (+) on the YEAR(Order Date) field.

QUARTER(Order Date) is added to the Rows shelf and the view updates.

v) On the Rows shelf, click the plus icon (+) on the QUARTER(Order Date) field
to drill down to MONTH(Order Date).Select Analysis > Create Calculated Field.

vi) In the calculation editor that opens, do the following:

o Name the calculated field, Quarter Date.
o Enter the following formula:
DATETRUNC('quarter' ,[Order Date])
o When finished, click OK.

38
The new date calculated field appears under Dimensions in the Data pane.

From the Data pane, under Dimensions, drag Quarter Date to the Rows shelf and
place it to the right of MONTH(Order Date).

The visualization updates with year values. This is because Tableau rolls date data
up to the highest level of detail.

vii) On the Rows shelf, right-click YEAR(Quarter Date) and select Exact Date.
viii) On the Rows shelf, right-click YEAR(Quarter Date) again and select
Discrete.

The visualization updates with the exact quarter date for each row in the table.

4. Find the categories which are losing money in each state

i) Connect to the Sample - Superstore saved data source, which comes with
Tableau.
ii) Navigate to a worksheet.
iii) From the Data pane, drag State to the Rows shelf.
iv) From the Data pane, drag Category to the Rows shelf and place it to the right
of State.
v) From the Data pane, drag Sales to the Columns shelf.
vi) Select Analysis > Create Calculated Field.
vii) In the calculation editor that opens, do the following:
o Name the calculated field, KPI.
o Enter the following formula:
SUM([Profit]) > 0
This calculation quickly checks if a member is great than zero. If so, it
returns true; if not, it returns false.
o When finished, click OK.

39
 The new calculated field appears under Measures in the Data pane.
viii) From the Data pane, drag KPI to Color on the Marks card.
You can now see which categories are losing money in each state.

5. How many orders the store had for a particular year

i) Use the COUNTD function to summarize the exact number of orders

COUNTD(Order ID)
ii) Break the visualization down by year.

Result:

Thus, the various inbuilt functions have been experimented successfully and
the outputs are verified.

40
 Working with Table Calculations
Ex. No.9.
Date:

Aim:
To study different types of table calculations available in Tableau to transform
values in a visualization

a) Difference from calculation b) Percent Difference calculation

c) Rank calculation d) Running total calculation

Concept:

Table calculations are a special type of calculated field that computes on the
local data in Tableau. They are calculated based on what is currently in the
visualization and do not consider any measures or dimensions that are filtered out
of the visualization.

Table calculations can be used for a variety of purposes, including:

• Computing difference between the current value and another

value(previous, next, first, last)
• Transforming values to show percent of total
• Transforming values to rankings
• Transforming values to percentiles
• Transforming values to show running totals

Procedure:

a) Difference from calculation

Create a table calculation

Step 1: Build the visualization

i) Open Tableau and connect to the Sample-Superstore saved data source.
ii) Navigate to a new worksheet.
iii) From the Data pane, under Dimensions, drag Order Date to the Rows
shelf.
The dimension updates to YEAR(Order Date).
iv) On the Rows shelf, right-click YEAR(Order Date) and select Quarter.
v) On the Rows shelf, click the + icon on QUARTER(Order Date).
MONTH(Order Date) is added to the shelf.
vi) From the Data pane, under Dimensions, drag Order Date to the Columns
shelf.
The dimension updates to YEAR(Order Date) again.

41
 vii) From the Data pane, under Measures, drag Sales to Text on the Marks
card.

Step 2: Add the table calculation

i) On the Marks card, right-click SUM(Sales) and select Add Table

Calculation.
ii) In the Table Calculation dialog box that opens, do the following:
o For Calculation Type: select Difference From.
o For Compute Using, select Table (across).
o When finished, click the X in the top corner of the Table
Calculation dialog box to exit it.
The calculation is applied to the values in the visualization.

42
b) Percent Difference calculation
In the Table Calculation dialog box that opens, do the following:

i) For Calculation Type: select Percent Difference From.

ii) For Compute Using, select Table (down).

c) Rank Calculation
In the Table Calculation dialog box that opens, do the following:
i) For Calculation Type: select Rank
a. Select Descending
b. Select Competition(1,2,2,4)
ii) For Compute Using, select Table (down).

43
 d) Running Total

i) Click the SUM(Sales) field on the Marks card and choose Add table
calculation.
ii) In the Table Calculation dialog box, choose Running Total as the
Calculation Type.
iii) Choose Table (Down) from the Compute Using list.

The highlighting in the view shows how this Compute Using value sets the scope
of the calculation in the view:

[Comparing the values in the original text view with the values in this view shows
that the result is correct. The monthly values ascend steadily and the December
value (484,247) is the same value as the column grand totals (from the Analysis
menu, select Totals > Show column grand totals).]

iv) Close the Table Calculations dialog box.

Result:
Thus the table calculations have been experimented successfully and the
output is verified.

44
 Creating Set and Group
Ex. No.10
Date:

Aim:

To create sets and groups to work with subsets of data.

Concept:

Sets are custom fields that define a subset of data based on some conditions.
Group is to combine related members in a field.
Procedure:

a) Create a set:
i) Connect to the Sample-Superstore saved data source, which comes with
Tableau.
ii) Right-click the Customer Name dimension in the Data pane and select
Create > Set.
iii) In the Create Set dialog box, type a name for the set. Eg. "Customers"
iv) Select the Use all option so the condition always applies to all values even
when new customers are added.
v) On the Condition tab, click By field, and then define a condition that only
includes customers when Sum of Sales is greater than or equal to 5,000.

vi) Click OK.

Create visualization:

i) Drag the new set from the Sets area at the bottom of the Data pane to the
Rows shelf.
ii) Drag Sales to the Columns shelf. The view now shows the total sales for
customers who have purchased more than 5,000 USD of product and the
total sales for all other customers.

45
 iii) Finally, click on the drop-down arrow on the Sum (Sales) field on the
Column shelf and select Quick Table Calculation > Percent of Total on the
context menu.

Create a combined set:

i) Drag the Customer Name field to the Rows shelf.

ii) Drag the Order Date field to the Filters shelf.
iii) In the Filter Field dialog box, select Years and click Next.
iv) In the Filter dialog box, select 2012 and click OK.

v) Back in the view, press CTRL + A on your keyboard to select all of the
customers.

46
vi) Right-click the selection and select Create Set.

vii) In the Create Set dialog box that opens, type a name for the set. Eg.
"Customers (2012)".
viii) Click OK.
ix) On the Filters shelf, right-click (control-click on Mac) Order Date and
select Edit Filter.
x) In the Filter dialog box, change the filter to only include 2013 instead
of2012, and then click OK.

xi) Again, press CTRL + A on your keyboard to select all of the customers.
xii) In the view, right-click the selection and select Create Set.
xiii) In the Create set dialog box that opens, type a name for the set. This set
will be called "Customers (2013)".
xiv) Click OK.
xv) In the Data pane, select both the Customers 2012 and Customers 2013
by holding the Ctrl key on the keyboard while selecting.

47
 xvi) Right-click the selection and select Create Combined Set

xvii) In the Create Set dialog box, type a name for the new set. In this
example, we'll call the set "Customers (2012 & 2013)".
xviii) Make sure the correct two sets are selected in the drop-down menus.
xix) Select the option to include Shared Members in Both Sets.

xx) Click OK.

Creating visualization with filter:

i) At the bottom of the workbook, click the New Worksheet icon.

ii) In the new worksheet, drag the Customer Name dimension to the Rows
shelf.
iii) Click the drop-down arrow on the Customer Name field on the Rows shelf
and select
Measure > Count (Distinct) from the context menu.

iv) Finally, from the Sets area of the Data pane, drag the Customers (2012
&2013) field to the Filters shelf. We can see that 437 customers purchased
products in both 2012 and 2013.

48
b) Creating Groups
i) Connect to the Sample-Superstore saved data source, which comes with
Tableau.
ii) In the Data pane, right-click a field and select Create > Group.
iii) In the Create Group dialog box, select several members that you want to
group, and then click Group.

iv) A default name is created using the combined member names, rename the
group by selecting it in the list and click Rename.

Including another group:

i) In the Data pane, right-click the group field and select Edit Group.
ii) In the Edit Group dialog box, select Include 'Other'.

49
 Edit a group:

i) In the Data pane, right-click the group field, and then click Edit Group
ii) In the Edit Group dialog box, select one or more members and drag them
into the group you want.
iii) Click OK.

Remove members from an existing group:

i) In the Data pane, right-click the group field, and then click Edit Group.
ii) In the Edit Group dialog box, select one or more members, and then click
Ungroup.
iii) Click OK.

Result:
Thus sets and groups have been created and the outputs are verified.

50
 Creating Dashboard
Ex. No.11
Date:

Aim:
To create and format dashboard for sales/profit analysis.
Concept:
A dashboard is a collection of several worksheets and other related
information in a single place. Dashboards are used for comparing and monitoring
a variety of data, all at once. Data in sheets and dashboards are connected. When
a sheet is modified, any dashboards containing it change, and vice versa. Both
sheets and dashboards update with the latest available data from the data source.

A blank dashboard will appear with the Data window replaced by four
sections: a list of existing worksheets in the workbook, dashboard objects, a
layout section, and a sizing section for customizing dashboard element sizes.

Query:
Create a dashboard showing the sales and profits for different segments and Sub-
Category of products across all the states.

Procedure:
Step 1: Create a blank worksheet by using the add worksheet icon that is located
at the bottom of the workbook.

i) Drag the dimension Segment to the columns area and the dimension
Sub-Category to the Rows area.
ii) Drag and drop the measure Sales to the Color area
iii) Drag and drop measure Profit to the Size area.
iv) This will plot a chart, and name this sheet as sales-profits.

Step 2: Create sheet2 to analyze details of the Sales across the various States.
i) Drag the dimension State to the Rows area and the measure Sales to
the Columns area.
ii) Add a filter to the State field to arrange the Sales in an ascending or
descending order.
iii) Name this worksheet as a sales state.

Step 3: Create sheet3 to display a map

i) Double click on geographical field country, state

ii) Drag and drop profit into the color area
iii) Drag and drop states into label
iv) Drag and drop sales into label

51
Step 4: Create a blank dashboard now by clicking the create new dashboard icon,
which is at the bottom of the workbook or Go to Dashboard Tab and Click New
Dashboard.

Step 5: Now, drag the 3 worksheets that were created in previous steps to the
dashboard. Once done, we can see three small icons near the top borderline of
the sales profit worksheet. Clicking the middle icon will show the prompt use as
Filter when mouse hovering is done over it.

Step 6: Now, as the last step, click the box that represents sub-category machines
and segment consumers in the dashboard. This is done to show only the states
where the sales happened for this amount/criterion of profit are filtered out in
the right pane. This shows that the sheets are linked.

Step 7: Add dashboard objects that add visual appeal and interactivity.

52
Output:

Result:

Thus the dashboard has been created successfully and formatted.

53
 Generating Charts
Ex. No.12
Date:

Aim:

To create various types of charts in Power BI.

Concepts:

Power BI offers a diverse array of visualizations to present data effectively.

1) Line charts portray trends and variations over time, ideal for tracking continuous
data.
2) Bar charts compare categorical data, making it easy to discern differences between
items.
3) Scatter plots showcase relationships between two variables, useful for identifying
correlations, and classifying data points.
4) Area charts display cumulative data trends, ideal for visualizing accumulated values
over time.
5) Pie charts showcase proportions of a whole, suitable for representing percentages or
portions of a dataset.
6) Treemap charts illustrate hierarchical data with nested rectangles, enabling users to
comprehend the distribution and hierarchical relationships within the data.
Procedure:
1) Connect the sample superstore data source Excel file to power BI environment.
2) From the Visualizations pane on the right-hand side, explore various types of charts
available in the Power BI.

Sample Input/Output:

1) Line Chart:
i) From the visualization pane select the “line chart”.
ii) Drag “Order Date” into X axis, “Sales” into Y axis and “Region” into legend.

54
2) Stacked Bar Chart:

i) From the visualization pane select the “Stacked Bar chart”.

ii) Drag “Sub-Category” into X axis, “Sales” into Y axis and “Region” into legend.

55
3) Area Chart:
i) From the visualization pane select the “Area chart”.
ii) Drag “Order Date” into X axis, “Sales” into Y axis and “Region” into legend.

4) Scatter Plot:
i) From the visualization pane select the “Scatter chart”.
ii) Drag “Sub-Category” into Values, “Sales” into X axis, “Profit” into Y axis, “Category”
into legend and “Order Date” into Play Axis.

56
5) Waterfall Chart:
i) From the visualization pane select the “Waterfall chart”.
ii) Drag “Order Date” into Category, “Sales” into Y axis and “Sub-Category” into
Breakdown.

6) TreeMap Chart:
i) From the visualization pane select the “TreeMap chart”.
ii) Drag “Sub-Category” into Category, “Sales” into Values and “Region” into Details.

57
7) Pie and Donut Chart:
i) From the visualization pane select the “Pie chart”.
ii) Drag “Region” into Legend, “Profit” into Values.
iii) Do the same procedure for “Donut chart”.

Result:
Thus, various charts are implemented and visualised in Power BI.

58
 Creating Tables and Matrix Visuals
Ex. No.13
Date:

Aim:

To understand the concept of Tables and matrix in Power BI and build them.

Concepts:

Tables and matrix are fundamental data visualization components in Power BI,
presenting information in a structured format for easy comprehension.

1) Tables display data in rows and columns, suitable for showcasing raw data or
detailed information. Users can perform sorting, filtering, and formatting
operations to present data precisely as required.
2) Matrix are similar to tables but provide additional hierarchical grouping, enabling
users to create pivot table-like layouts. Matrices are ideal for summarizing data,
presenting subtotals, and facilitating cross-tabulation.

Both tables and matrices empower users to explore and analyse data efficiently, offering
valuable insights and supporting data-driven decision-making.

Procedure:
1) Connect the sample superstore data source Excel file to power BI environment.
2) From the Visualizations pane on the right-hand side, Tables and matrix are
present.

Sample Input/Output:

1) Creating a Table:
i) Select the Table icon from the visualization pane.
ii) Add columns by simply dragging the fields into it.

59
2) Number and Conditional Formatting a Table:

i) In the Visualization pane choose “Format your visual”.

ii) Click on style presets field to quickly customize your table from the given options.
iii) To apply Number formatting to your table
a) Click on “Specific Column”.
b) Select “Sales” from the Series box.
c) Click on “values” field.
d) Set the Display units as “Thousands”.
iv) To apply Conditional formatting to your table
a) Click on “Cell Elements” field
b) In the “Series” drop down box select which column you want to apply
customization.
v) Select “Sales” from the Series box and turn on the “background color” option.
vi) Select “Profit” from the Series box and turn on the “icons” option.
vii) Select “Quantity” from the Series box and turn on the “Data bars” option.

60
3) Creating Matrix
i) Select the matrix icon from the Visualization pane.
ii) Drag “Sub Category” into Rows, “Region” into Columns and “Sales” into Values

61
4) Conditional Formatting of the matrix

i) By using the same logic applied above to format a table create a customize
the matrix.

Result:
Thus, the concept of Tables and Matrix have been implemented to organize
and visualize the data effectively.

62
 Working with DAX Queries
Ex. No.14.
Date:

Aim:

To work with different inbuilt DAX functions and perform tasks in Power BI.

Concepts:

DAX (Data Analysis Expression) is a library of functions and operators that can be
combined to build formulas and expression used by Microsoft Power BI. DAX is also known
as function language, where the full code is kept inside a function.

By using DAX, you can add three types of calculations to the model:

1) Calculated columns
2) Measures

Calculated Column Measure

Expands table by creating new column. Summarize the data into a single value.
Stored along with the table. Consumes Calculate at runtime / Stores
Memory. temporarily.
Less analytical capacity. Rich analytical capacity.
Eg: Eg:
Profit = [Sales amount] – [cost amount] Total profit = Sum([Sales amount]) – Sum
([cost amount])

Syntax of DAX Query:

63
 A. The name of the measure or calculated column
B. The equal-to operator (“=”) indicates the start of the formula
C. A DAX function
D. Opening (and closing) parentheses (“()”)
E. Table references
F. Parameter or Column of the table.
Note that each subsequent parameter in a function is separated by a comma (“,”)

Sample Input/Output:
To create a measure or calculated Column
• Click on Modelling on top bar of power BI.
• Select ‘New Measure’ for creating measure and ‘New Column’ for creating
a new column

1) Find Minimum sales from each category

a. Create a new measure and name it Minimum sales

b. Now in the editor that opens up type in
Minimum Sales = MIN(‘Order’[Sales])
c. When finished click on tick mark or press Enter.

d. Select ‘Category’ and ‘Minimum Sales’ from the Data pane.

64
2) Find out how many orders were placed for every year
a. Create a new measure and name it total count.
b. Now in the editor that opens up type in
Total count = COUNT (‘Order’[Order ID])

c. Create a column chart with ‘Order Year’ in Y-axis and ‘Total Count’ in Y-axis
and set ‘data labels’ On.

3) Find the states which has profit or loss and visualise it in different colours

a. Create a new measure and name it KPI.

b. Now in the editor that opens up type in
KPI = SUM(‘Order’[Profit])>0

c. This KPI gives return a Boolean value for the given field.
d. Now create colour measure to give red to negative value and green to positive value

65
 e. Create a column clustered chart with ‘State’ in Y-axis, ‘Sales’ in X-axis and ‘Profit’ in
tooltip.
f. Now go to ‘Format your visuals’ in the visualisation pane.
g. Click on Bars field and select conditional formatting by clicking on 𝒇𝒙 icon.
h. Select ‘Format style’ to Field value.
i. Select ‘colour’ measure in ‘What field should we base this on?’.

j. Click ok to get the final output.

4) Find out the average ship time for various states.

a. Create a new column and name it Date Difference.

b. Now in the editor that opens up type in
Date Difference = DATEDIFF(‘Order’[Order Date], ‘Order’[Ship Date], DAY)

c. Select ‘States’ category and ‘Date Difference’ from the Data pane.

66
5) Filter the order number from the Order ID

a. Create a new column and name it ID.

b. Now in the editor that opens up type in
ID = RIGHT(‘Order’[Order ID], 6)

c. Select ‘ID’ from Data pane to view unique Order number.

Result:
Thus, various DAX Functions have been used and outputs are verified successfully.

67
 Creating Storyboard
Ex. No.15
Date:

Aim:

To create a storyboard in power BI to visualize data insights effectively.

Concepts:

A storyboard is a feature that allows you to create a sequence of report pages to tell
a data-driven story. It's a way to organize and present your data visualizations in a logical
order to communicate insights effectively. Here are the key concepts of storyboard in Power
BI:

1. Sequential Presentation: A storyboard in Power BI enables you to arrange report

pages in a specific sequence to create a narrative flow.
2. Narration and Annotations: You can add text boxes, titles, and annotations to each
report page to provide context, explanations, and key takeaways.
3. Interactive and Exploratory: Even though a storyboard has a defined sequence, it
retains the interactive nature of Power BI reports. Users can still explore and interact
with the data visuals on each page.
4. Navigation Controls: Storyboards come with built-in navigation controls, allowing
viewers to move forward and backward through the pages and explore the data story
at their own pace.

Procedure:

1) Open Power BI and load ‘ Sample-Superstore.xlsx ’ file to it.

2) On the empty page select ‘Format your visuals’ in the visualization pane and select
'Canvas Background’
3) Select any Background colour of your choice and set ‘Transparency’ to 0 (Colour used
for the output is ‘#083E58’).
4) Select ‘Insert’ option on the top bar and select ‘Rounded rectangle’ from ‘Shapes’
menu.
5) Resize the shape by stretching the shape and reducing the size (Look output for
reference).

68
6) After selecting the shape visual you can customize the visual in a way that it suits
your dashboard.
a. In the Format pane change the shape to ‘Parallelogram’.
b. Change the colour of the visual from the ‘Fill’ menu of the ‘Style’ option, choose a
matching colour to the background (Colour used in the output ‘#609CA7’).
c. In the Style option, On the ‘Text’ Menu and type ‘Dashboard’ in the text field.
d. Increase the font size and bold the letters.
7) In the bottom you will have the ‘page1’ tab right click on it and select ‘Duplicate Page’
8) Now rename ‘Duplicate of Page1’ to ‘Page 2’ by simply right clicking the ‘Duplicate of
Page1’ tab.
9) In the Page1 add ‘Blank button’ by
a. select the ‘Insert’ option
b. Select ‘Blank’ from ‘Buttons’ menu
c. Place the button to the top right corner.
10) Click the button and in the ‘Format’ pane open ‘Style’ menu.
11) Select Text value ‘On’ and fill in ‘Go to page 2’.
12) Copy and paste the button in the Page2 and change text value as ‘Go to page 1’.

13) Click on the button on the page 1

a. Turn On the Action menu
b. Select Type as ‘Page Navigation’.
c. Select Destination as Page 2.

69
14) Click on button on the Page 2
a. Turn On the Action menu
b. Select Type as ‘Page Navigation’.
c. Select Destination as Page 1.
15) Now create charts and graphs suited for every page based on your analysis, here are
the steps performed to get the output image
a. Visuals in Page 1
i. Create a filled map visual with ‘Region’ as legend.
ii. Create a stacked bar chart with ‘Sub category’ in X axis and ‘Sales’ in Y axis.
iii. Create a pie chart with ‘Profit’ in Values and ‘Region’ as legend
b. Visuals in page 2
i. Create a Waterfall chart with ‘Order Date’ in Category, ‘Sub Category’ as
breakdown and ‘Sum of Sales’ in Y axis.
ii. Create a Line chart with ‘Order date’ in X axis, ‘Sum of profit’ in Y axis and
‘Region’ as legend.
iii. Create a Line and Clustered column chart with ‘Order Date’ in X axis (remove
‘quarter’, ‘month’ and ‘day’ from the X axis), ‘Sales’ in column Y axis and Profit
in Line Y axis.
iv. Create a Scatter chart with ‘Sub category’ in X axis, ‘Profit’ in Y axis, ‘Sales’ in
size and ‘Region’ as Legend.
16) The Final step is to format our graphs to make it look cleaner and more matched to
our background.
a. Select any one of the visual.
b. In the ‘Format your visual’ option from the Visualization pane, select ‘Values’
option from the X axis menu and set colour to be white
c. Similarly change colour of Y axis values to white.
d. In ‘Format your visual’ option select ‘General’ Menu.
e. Select Background option from Effects menu.
f. Select the Background colour to be same as your shape colour (In this case the
colour is ‘#609CA7’) and set transparency to 70%.
g. From ‘Home’ Tab on the top select ‘Format painter’ option present as little paint
brush icon.
h. After selecting ‘Format painter’ click on another visual present in the page.
i. Repeat the above step to all other graphs and charts.

70
SAMPLE INPUT/OUTPUT:

i) Page 1

ii) Page 2

Result:
Thus, an interactive storyboard has been created for presentation purpose.

71