Visualizing KPIs is the most important part of any dashboard. So I make sure that I have put the best of my efforts into making them look neat and attractive.
In order to show progress KPIs like % Compliance, % Achievement we either use a bar chart or donut chart or BANs (Big-Ass Numbers) and what not, which is good- considering the fact that they use less space and are easy to understand.
Gauge charts are one of the most attractive ways of representing those same KPIs but in a different manner.
Now I know what some of you guys must be thinking-
'We need to create complex table calculations to make that'
'We need to create custom data for it'
'It has a performance impact'
Well not with this method. I have used simple calculations to create the gauge chart.
You just need to follow the following steps:
Step-1: Create a donut
Use dual axes of MIN(0) to create a donut. I am hoping everyone here knows how do do that. In case you don't, you can see ho it's created here.
Step-2: Creating necessary calculations
The % value that I need to show in the gauge, let's call it by the name of the field- Value
Change the default number format to percentage.
Now, we need to create 8 calculations as follows:
IF [Value]<.5 THEN [Value]
IF [Value]<.5 THEN .5-[Value] END
IF [Value]>.5 THEN 1-[Value]
IF [Value]<=.5 THEN .005 END
IF [Value]>.5 THEN .005 END
Step-3 : Selecting the slices
Put Measure Names into the filter and select these above measures. Dummy will not be in the filter as it is a dimension.
Step-4 : Arranging the slices
Put Measure Names into the color and Measure Values into angle for both the inner and outer pies. Now arrange the measure values in the following order from top to bottom:
Which is going to look something like this:
Now color the slices as follows:
1. Needle1 and Needle2 will be black
2. A and C will be blue
3. B and D will be grey
4. E will be white
You can already see something getting built up like this:
Step-5 : The final touch.
Now for the finale, add the Dummy field into the detail of the inner pie on the marks card. Then click on the detail icon and select color. You will notice that the Measure Names and Dummy have created a temporary combined field to color the inner pie.
Now color the Needle1, Dummy and Needle2, Dummy in black and rest in white.
and with a few tweaking up the label, you will get something like this:
You can play around with the value of E to get different results.
You can also refer to the workbook here
Will soon be updating the gauge with the condition based colors.
Hope you enjoyed this one
Keep coming back for more!