Do you have KPI's on your dashboard? Would you like to draw attention to low performing KPI's? What if you wanted to highlight low performing metrics? There are multiple ways to identify this. One method I would like to show, that you may not be aware of, is by applying a calculated field to a reference line.
Will Perkins has an good example of how to automatically highlight your KPI's based on a formula in his public dashboard here. Look at the Enhancing Those Sweet KPI's tab in particular.
I will walk you through an example of this using a Date Parameter. My dashboard is here. I am using the Sample Superstore data set. I am also using the following calculations focusing on the Segment instead of Region that Will's dashboard uses.
I created a Parameter from the Order Date column in the data set. For the Display format, I used the MMM YYYY Custom format.
I then created a calculated field called Current Month and pointed it to this Date Parameter;
Current Month
[Date]
I display this Parameter and point it to the maximum date or latest date in my data set.
I then created the following calculated fields;
Previous Month
Date(DATEADD('month',-1,[Latest Month]))
Sales (This Month) Segment
{Fixed [Segment] : SUM({exclude datetrunc('month',[Order Date]) : sum(
If DATETRUNC('month',[Order Date])=DATETRUNC('month',[Latest Month])
THEN
[Sales]
Else 0 end)})}
Sales (Previous Month) Segment
{Fixed [Segment] : SUM({exclude datetrunc('month',[Order Date]) : sum(
If DATETRUNC('month',[Order Date])=DATETRUNC('month',[Previous Month])
THEN
[Sales]
Else 0 end)})}
The above calculations will point to the latest date similar to the view below;
The next calculated fields will be used for the KPI view;
MonthDiff % Segment
(Sum([Sales (This Month) Segment])-Sum([Sales (Previous Month) segment]))/Sum([Sales (This Month) Segment])
NegativeMonthDiff Segment
IF [MonthDiff % Segment]<0 then '▼'
Else ''
END
PositiveMonthDiff Segment
IF [MonthDiff % Segment]>0 then '▲'
Else null
END
I started by placing Segment on the Columns shelf.
I then placed the Sales (This Month) Segment calculation on the Text Marks card.
I right-clicked on the Rows shelf and selected New Calculation. I then entered two double quotes. This is for the Reference Lines later.
I right-clicked on the Rows shelf again to create another New Calculation. I entered Avg(0) here. This trick centers the text.
Next, I changed the Chart type from Line to Text.
I placed the following calculated fields on the Text Marks card;
Segment
MonthDiff % Segment
NegativeMonthDiff Segment
PositiveMonthDiff Segment
I moved these around by clicking on the Text Marks card so that it appeared as below;
I changed the font size for the first two pictured to 22.
I also formatted the MonthDiff% Segment as a percentage and formatted the others as currency without decimal places.
I also added MoM at the end of the text field.
Back on my worksheet, I removed all of the grid lines, column dividers etc. on the page.
I then created the following calculations in order to use them for the Highlighting Reference lines;
Negative MonthDiff % Segment
IIF ([MonthDiff % Segment]< 0.0,0, null)
Positive MonthDiff % Segment
IIF ([MonthDiff % Segment]>=.75,0, null)
I placed both of these calculations on the Detail Marks card.
On the Analytics tab I dragged the Reference line to the visual and selected Table when I was prompted on the Add a Reference Line pop-up menu.
On the Edit Reference Line, Band or Box pop-up menu, I set up the following configuration for the first reference line;
After I clicked OK, I then dragged another reference line onto the worksheet and configured it as follows;
Here is the view if I filtered by November 2020;
Notice I have over 75% for Consumer and pink for Corporate / Home Office.
The only other thing I like to add is a custom Header, as shown in the calculated field below;
Header
" Sales by Segment for " +DATENAME('month',[Date])+" "+STR(Year([Date]))
I placed this on my Column shelf before Segment.
I then hid the Header for Segment and hid the Field labels for this Header calculated field.
I also hid the headers for both metrics on the Rows shelf.
I also formatted the Header calculated field to have White text and dark gray shading.
If you select different dates from the Parameter, you will notice the colors change for the different KPI metrics.
Comments