Part -2 What is KPI? How to build a KPI Tableau Dashboard
You can check out part 1 of my tutorial here.
Introduction
This particular table you are seeing now has the few elements, this sales column has a faint bar graph in the background as well as triangle indicators that tells whether the sub-category meets or exceed a threshold. The sales trend column has a Sparkline, a very small line chart without labels or co-ordinates, this is meant to show general activities or trend over time. The profit column shows conditional text callers, where negative values are orange and positives are blue. The discount column has a conditional column based on the discount that are meeting or exceeding the threshold.
We will cover how to create a threshold and conditional formatting of the graphs like above.
In this article we will answer the remaining question
1.How many products reaches the threshold of sales and profit of current year?
2.Is the sales trend varies for each category?
3.Is the discount above 20% increase the profit?
Part -1
a)Embedded bar with KPI: Does the current sales exceed the sales of previous year?
Tricks
1.Bar/Shape Dual Axis
2.Caluclated Field per color/shape
Drop Sub-category to rows and sales to columns and make the chart as bar from automatic . Please note that I’ve gone ahead and created three parameters that we can use further in this article. One parameter for profit threshold, one for sale and one for discount threshold.
Before working on this threshold let’s create the parameters like the image below
For the first question, we simply want to identify if any of the sub categories that have the sales meet or exceeds the sales threshold, so how can we do that?
- Create Calculated field if sales exceeds sales threshold and name it as “Sales Threshold Above”
if SUM([Sales])>= [Sales Threshold]
then SUM([Sales])
END
2.Create Calculated field if sales less than sales threshold and name it as “Sales Threshold Below”
if SUM([Sales])< [Sales Threshold]
then SUM([Sales])
END
3. Create Calculated field with Triangle or circle indicator and name it as “Threshold indicator”
if SUM([Sales])>=([Sales Threshold Above])
Then “▲”
END
Create Calculated field AXIS
AXIS is one popular way when you have to work on multiple measures in a single graph is by creating our own axis, when we add axis for rows or columns we get marks card for each axis which means we can independently manage our measures. Let’s create an axis as below: and name it as “Axis”
Change the bar color to grey before adding the Axis in column. Axis mark card will appear as follows. Select Shape and choose the shape you want. I chose triangle shape.
Drop the “Threshold indicator” calculated field in color of Axis card. Drop “Sales Threshold Below” and ““Sales Threshold above”” in Label.
Formatting the graph, follow the numbers as follows. And hide all the titles and headers.
Double click the blue color triangle and assign the palette of blue to white and leave the orange just like that. Now the Embedded graph should look like below:
b) Simple KPI table : Does the current profit exceed the profit of previous year?
For the second question we already created a Profit threshold (parameter), now create calculated field like we created for sales. Replace sales with profit. Instead of putting sales in column , drop the profit in colors and in label and add the calculated fields in Label. The graph will look like below. (Instead of triangle I have used tick and wrong symbol)
Part -2 How to create Sparkline that demonstrates sales trend?
Follow the steps and the formatting in the image as follows:
- Drag the order date to column and change to month
- Add sub- category and sales to rows
- Once done, you will see the line chart but some categories might have flat range, Its due to uniform axis for all the sub categories. Right click and change to independent
Part -3 Is the discount above 20% increase the profit?
we have already created a threshold for discount in the beginning. since we are going to compare discount measure against profit measure. We are going to work on multiple measures. You are right , again we are going to use the AXIS pills here not just one but 2.
Create an axis calculated field and name it is -1
MIN(-1.0)
We will create calculated field that counts the discount which has more than 20% and name it as “Discount Threshold above or below”
if AVG([Discount]) >= [ Discount Threshold]
then “Equal or Above”
ELSE
“Below”
END
Now let’s create the graph and formatting
- Add the axis to column and sub-category in rows. Make the graph as Gantt.
- Drag the discount to label and make the alignment to right. Drag -1 to size and “Discount Threshold above or below” to colors.
- Right click the axis on the chart and edit the fixed to 0
After hiding the titles and axis header , the graph should look like below
Create a Sales_KPI_Dashboard and add all the finding of article 1 and article 2 to your dashboard.
Summary & Findings
- Does the current profit exceed the profit amount of previous year?
Yes the profit has increased by 14.2% compared to previous year
2.Does the current sales exceed the sales of previous year?
Yes the sales has increased by 20.4% compared to previous year.
3.How many products reaches the threshold of sales and profit of current year?
Profit Threshold : Out of 17 categories ,3 categories were below profit threshold
Sales Threshold: Out of 17 categories, 5 categories were above sales threshold
4.Is the sales trend varies for each category?
The sales trend varies for each category , which helps the organization for future budgeting and financial analysis method
5.Is the discount above 20% increase the profit?
There are 4 categories which has discount more than 20%, in which the profit of 2 categories went in negative.
Recommendation
The data shows that discount can boost sales but profit suffer. The lesser the discount , the lesser the sales too. In order to increase sales and profit ,the median discount can range from, 10 to 20%.
This discount rate would be ideal to improve the profit and sales of each category.
Check out my Sales_KPI_Dashboard in tableau public
