The third sheet: Customer Details

In the third sheet you will make a scatter plot and a pivot table that focus on customers. Then you will add filter panes so you can filter by date and manager.

By now you have so much experience from creating dimensions, measures, and visualizations that you do not need detailed procedures anymore. If you need reminders of what you have learned so far, you can refer back to the previous topics.

You will start your work in standard edit mode. Once you create your visualizations, you will use advanced edit mode to customize them.

Adding the scatter plot

The next visualization is a scatter plot showing the Customer Sales and Quantity. Make sure that Advanced properties is toggled off.

A scatter plot is used to visualize the relationship between two or three measures.

For more information, see Scatter plot.

In this case, the measures compared are Sales and Quantity. Each bubble represents a Customer dimension value.

Do the following:

1. Go to Fields, and drag the Customer field to the sheet and add it as a dimension.

2. Add Sales and Quantity as measures. The Autochart turns the visualization into a scatter plot.

3. Go to Presentation > Axis. Set both the X and Y-axes to start at Zero. This removes negative values.

4. Give the scatter plot a title: Customer Sales and Quantity.

Adding the Customer KPIs pivot table

The pivot table presents dimensions and measures as rows and columns in a table. In a pivot table you can analyze data by multiple measures and in multiple dimensions at the same time.

You can rearrange the measures and dimensions to get different views of the data. The activity of moving measures and dimensions interchangeably between rows and columns is known as 'pivoting'.

One of the advantages of a pivot table is the interchangeability, that is, the ability to move row items to columns and column items to rows. This flexibility is very powerful and enables you to rearrange the data and have several different views of the same data set.

Depending on what you want to focus on, you move the dimensions and measures to bring forward data of interest and hide data that is either too detailed, or irrelevant for the analysis.

For more information, see Pivot table.

Do the following:

1. Click below the bar chart to add a new visualization.

2. Add Customer as a dimension.

3. Add Sales and Sales Qty as measures.

4. Under Properties > Visualizations, select .

5. Give the pivot table a title: Customer KPIs.

Now you have a very simple pivot table.

The remaining measures, for the last three columns, need to be created in advanced edit mode.

Customizing the pivot table in advanced edit mode

In the top right, toggle on Advanced options to work in advanced edit mode.

Creating measures with expressions

Do the following:

1. Select the pivot table.

2. Go to Data > Measures.

3. Margin Percent measure:

   1. Click Add and click . Insert this expression:

      (Sum(Sales) - Sum(Cost)) / Sum(Sales)

   2. Click Apply.

   3. Under Label, type: Margin Percent.

   4. Set Number formatting to Number (12.3%).

4. # of Invoices measure:

   1. Click Add and click . Insert this expression:

      Count (Distinct [Invoice Number])

   2. Click Apply.

   3. Under Label, type: # of Invoices.

5. Average Sales per Invoice measure:

   1. Click Add and click . Insert this expression:

      Sum(Sales)/Count(Distinct [Invoice Number])

   2. Click Apply.

   3. Under Label, type: Average Sales per Invoice.

   4. Set Number formatting to Money.

Adjusting the other measures

Do the following:

1. Go to Data > Measures.

2. Click Sum (Sales) and set:

   • Number formatting to Money.

   • Label to Sales.

3. Click Quantity and set:

   • Number formatting to Number (1,000).

   • Label to Quantity.

Adding dimensions as rows

Do the following:

1. Go to Data > Dimensions. Under Row, click Add.
2. In the list, select Product Group.
3. Select Add again and add a Product Type row.

You are now able to look at the sales for individual customers by product group and type. Click Customer, Product Group or Product Type, or selecting individual items in the table, filter the selections viewed in the table. By moving Product Group or Product Type to Measures and filtering you can achieve differing views of the data presented.

Adding filter panes

Now you will add two filter panes, so you can filter by time period and manager.

Do the following:

1. Add the filter pane Period.
2. Add a new filter pane with the dimension Manager.

This sheet is complete. In the top right corner, click to move to the Customer Location sheet.