Designing dashboards with calculated measures

If you are designing a dashboard and you wish to predefine calculations within a tile using Calculated Measures, then there are three concepts that you need to understand:

  • Data is stored in tables

  • Tables can be joined together in different ways

  • Different joins produce different results

Don’t worry, you don’t need to define, create, or adjust the tables. However, you do need to decide how you are going to join the tables within your calculation, dependent upon the results you are trying to achieve. With Calculated Measures, Apteco software attempts to do this for you.

This document is designed to help you get the result you are expecting. It takes a step back to explain the basic concepts of how data tables are related, and how the three main join types that Calculated Measures uses, impact the results that you get.

Joins

We all want data from many different sources to be in one place, with the ability to use it all to understand our customers, their purchases, and their interactions with us. To enable this insight, whatever the goal, data from different tables needs to be ‘joined’ together in a certain way to get an accurate account of activity. There are three types of joins commonly used for customer analysis.

Note: Different joins provide different results. This document explains what we mean when we talk about tables and goes on to explain three join types and provides examples of their application.

The three join types discussed are:

  • Right Join

  • Inner Join

  • Left Join

How to implement the different joins within a calculated measure tile

LEFT JOIN: Including all relevant records from the left table and only the related records from the right table

We do this by editing the tile where the calculation is being applied and under OPTION select ‘Change the tile to select via’. Under this option nominate the Left Table that the value being used in the calculation originates from and tick the box to ‘Return all values from the ‘xxxxx’ and any matching transaction values.

Note: The ‘xxxx’ changes to reflect the nominated table.

In the example below the table being selected is the ‘Households’ table.

Note: Avoid allowing the user to apply a filter to anything other than the Left Table or the calculation doesn’t provide the expected result.

INNER JOIN: Only include those records from the left table that are related to records in the right table, and at the same time only include records from the right table that are related to records in the left table

We do this by editing the tile where the calculation is being applied and apply two filters. The first selects all records from the Left table the second selects all record from the right table. These filters are designed to ensure that only the interrelated records from both tables are selected. In the following example the left table is ‘Households’ and the right table is ‘Loyalty Members’. A selector attribute has been chosen from each and all the categorise within those attributes (State, and Title) have been applied as a filter.

RIGHT JOIN: Including all relevant records from the right table and only the related records from the left table

This calculation happens without the need for any adjustment by the dashboard designer.

Note: Avoid allowing the user to apply a filter to anything other than the Right Table or the calculation doesn’t provide the expected result.