Have you ever had the need to create a KPI that represents the # of *dimension* that represents the X% of a *measure*. For example:
- Number of customers that represents the 80% of sales
- Number of products that represent 50% of profit
- Number of countries that represent 90% of the quantity of products sold
This happened to a coworker of mine and thanks to our collective input we managed to get to a solution. In this blog, I will show you how to hack your way through Tableau to achieve this.
Let’s start creating our KPI!
Let’s take Sample Superstore and let’s try to answer: what is the number of customers that represents 80% of sales?
Just to visualise, let’s start by seeing a descending list of all the customers with their respective sales
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.46.49.png)
First, we will like to know what percentage the customers represent from the total sales. For that, we will do the following:
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.42.47.png)
If you do a running sum of the sales per customer and divide it by the total amount of sales you will have a list of customers and how much their sales represent (%) from the total amount of sales.
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.47.44.png)
Now we will want to count how many of these customers represent our 80%:
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.54.14.png)
Your [Count] list will have a list of 1’s until your [Running Percentage] surpasses 80%.
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.55.01-1024x384.png)
Now what you have to do is do a running sum of this count and you will have (by the end of the list) the number you have been looking for.
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.58.00.png)
In this case 395 customers
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-13.58.31-1024x514.png)
To convert this number to a KPI you will need to remove all of your measures but the last one, [Running Sum of Count]. You will also create a [LAST filter].
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-14.02.19.png)
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-14.02.41.png)
Add this (True) [LAST filter] to the view and you will have a first glance of your KPI
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-14.03.55.png)
Right-click on [Customer Name] to remove the tick of ‘Show Header’. Change or hide your title and format your view to get the KPI that you need.
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-14.08.21.png)
TL;DR
![](https://theinformationlab.nl/wp-content/uploads/2023/02/CleanShot-2023-02-23-at-14.28.21.png)
Now you have all the tools necessary to create the KPI that you need if you follow the same principle adding your custom measures/dimensions!
For this and more tips visit our website: https://theinformationlab.nl/en/blogs/