Skip to main content

The third sheet: Customer Details

This sheet focuses on customers.

By now you have so much experience from creating dimensions, measures, and visualizations that you do not need detailed procedures anymore. The only exception will be when you make changes in the properties panel. If you need reminders of what you have learned so far, you can refer back to the previous topics.

Customer Details sheet when editing
Customer Details sheet when editing

Adding filter panes

Do the following:

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

Adding the scatter plot

The scatter plot uses the dimension Customer and the measures Sales and Quantity. You need to create the measure Quantity, and then save it as a master item. Use the field Sales Qty and the aggregation Sum. Because the field Sales Qty consists of two words, you need to enclose it with brackets: [Sales Qty] in the expression. The expression should look like this: Sum ([Sales Qty])

In the properties panel, at the bottom of Appearance, use the Range setting for the Y-axis and X-axis to exclude the negative part of the axes.

You probably noticed that two measures were added to the scatter plot. The scatter plot is used to visualize the relationship between two or three measures. In this case the measures compared are Sales and Quantity. Each bubble represents a Customer dimension value. The visualization should be named Customer Sales and Quantity.

Adding the Customer KPIs table

The table named Customer KPIs uses the dimension Customer.

You add more columns to the table from Data in the properties panel: use the measures Sales, Quantity, and Margin Percent that are available as master items. Add them in that order to get the same order as in the screen shot.

The remaining measures, for the last two columns, need to be created:

  • For the measure # of Invoices, use the following expression:

    Count (Distinct [Invoice Number])

  • For the measure Average Sales per Invoice, use the following expression:

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

Tip:

The qualifier Distinct is used in two of the expressions. By using Distinct, you ensure that an invoice number is only counted once, even if it occurs several times in the data source. Distinct sorts out unique numbers. Note that Distinct must be followed by a space before the field name.

Adjusting the number formatting

Do the following:

  1. In the properties panel, click Data.

  2. Click Sales and set Number formatting to Money.

  3. Click the measure to close it.
  4. Click Quantity and set Number formatting to Number (1,000).

  5. Click the measure to close it.
  6. Click Margin Percent and set Number formatting to Number (12.3%).

  7. Click the measure to close it.
  8. Click Average Sales per Invoice and set Number formatting to Money.

  9. Click the measure to close it.

Converting the Customer KPIs table to a pivot table

Changing the Customer KPIs table into a pivot table enables you to include further dimensions or measures and reorganize them to analyze the data in a more flexible and useful way.

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.

Customer Details sheet after conversion

Customer Details sheet after conversion

Converting the table

Do the following:

  1. In the assets panel, click 5 to open Charts.
  2. Drag a pivot table onto the center of the Customer KPIs table and select Convert to: Pivot table.
  3. In the properties panel to the right, under Data, click Add data and then Row.
  4. In the list, select Product Group.
  5. Select Add data again and add a Product Type row.
  6. Add the title Customer KPIs to the visualization.

  7. Click Edit Done in the toolbar.

You are now able to look at the sales for individual customers by product group and type. By clicking Customer, Product Group or Product Type, or selecting individual items in the table, you can 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.