Many a times there is a need in our analyses to aggregate the measures at a different level than what is shown in the view. For example, if we drag a column called Region and then Sales then the measure value will be returned as per each Region:
However, if we introduce another column called Customer Name, then the sales figures will be grouped by Region and Customer Name:
Now suppose if we want to calculate the count of customers billed or count of customers as per a type of billing and we want to calculate that without keeping customer column in the view.
For example, count of customers for every type of billing over every region, then it becomes an issue as we don’t want to show customer column but the aggregation needs to be done at customer level.
Requirement
There was a requirement to calculate the count of customers for every type of Invoice type for every region.
Mock data looks like below:
There are Region, Customer ID, Invoice Number, Invoice Type and Period columns.
Solution:
After connecting to Data Source, the first step was to create a calculated field called “Count of Invoice”. It counts the distinct Invoice Number.
Now create another calculated field called “Count of Invoice Type” on top of the calculated field “Count of Invoice”. This field will be used to count of type of Invoices.
Now create analysis in Tableau with Customer ID column in the view.:
Please note that you will have to include Customer ID into the view otherwise the result of Count of Invoice Type will always be 1 as shown below:
Go to Analysis Menu->Totals->Add All Subtotals:
This will add subtotals for each Region.
Now we have got the numbers that we wanted. But the Issue now is that we want to get rid of the Customer ID column from the View.
Next step is to select all Customer ID values (Shift + Left Mouse Click or Click on the Customer ID value and CTRL+A):
This will select all rows.
Now right click anywhere on the Customer ID values and select Hide. This will keep the calculation still at the Customer ID level but will hide the rows:
The result we will get looks like below:
The above Result looks good but still there is Customer ID column visible in the view.
Right click on any Total text and uncheck Show Header:
This will remove the Customer ID column from the view and we will be left with the result:
Please note that the Customer ID column is still there in the analysis but not visible in the front View.
Get Started with a Tableau Trial today
Some more exciting products that we are partner with:
Download a Lavastorm Dataverse Trial Today & Wherescape