In my last post, I showed a dashboard in SSRS 2005 that included sparklines, inline bar charts and bullet charts. As I mentioned there is good MSDN article on how to do inline charts but the Bullet chart took a little more thought.
As one of my colleagues has pointed out, these aren’t bullet charts in the truest sense that Stephen Few designed them as they don’t show the “bullet’s path” running through them, however I think they serve the same purpose albeit in a simplistic way
The charts are made using 100% stacked bars with three data values for each “zone” of your chart:
- The section below your marker;
- The marker itself (which of course only shows if it falls in that zone); and
- The section above your marker.
So in the Speciality Bike Shop row in the example above, the three values that add up to the first zone of 25 are 11, 1 and 13 with the colour set as Dark Grey, Black and Dark Grey respectively. Whereas as Value Added Reseller is made up of three zones with values 0, 0 and 25
Likewise, the 2nd zone for Speciality Bike Shop is made up of 0, 0 and 64 whereas VAR is 42, 1 and 22 this time with light grey, black, light grey
So how do we come with the numbers? It’s just a case of calculating the size of the zones as a percentage of the whole and deciding whether the marker should show. Lets follow the MDX query through to explain
First lets define our key measures: the zone limits, and the actual value for our marker. I’m using constants for the zone sizes but you could easily have this coming from your data:
WITH //replace this constant with your Zone 1 measure MEMBER [Measures].[Zone1] AS 300000 //replace this constant with your Upper Zone 2 measure MEMBER [Measures].[Zone2] AS 1067000 //replace this constant with your Upper Zone 3 measure MEMBER [Measures].[Zone3] AS 1200000 //Actual measure MEMBER [Measures].[Actual] AS [Measures].[Reseller Sales Amount]
Next we need to calculate which zone the marker will fall in – we set a boolean for each which is also used as the marker for the chart.
//Are we in the zone 1 MEMBER [Measures].[ShowZone1Marker] AS IIF( [Measures].[Actual] < [Measures].[Zone1], 1, 0 ) //Are we in Zone 2 MEMBER [Measures].[ShowZone2Marker] AS IIF( ([Measures].[Actual] >= [Measures].[Zone1] AND [Measures].[Actual] < [Measures].[Zone2]), 1, 0 ) //Are we in Zone 3 MEMBER [Measures].[ShowZone3Marker] AS IIF( [Measures].[Actual] >= [Measures].[Zone2], 1, 0 )
Then we need to calculate the size of the lower and upper parts of each zone, lower first:
MEMBER [Measures].[Zone1a] AS (([Measures].[Actual]) * [Measures].[ShowZone1Marker] / [Measures].[Zone3] * 100) - ([Measures].[ShowZone1Marker] * [Measures].[ShowZone1Marker])
the logic is:
The actual value subtract the previous zone limit (not required in zone 1), multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart. The bottom line removes one from the value to allow for the marker.
The upper part of zone 1:
MEMBER [Measures].[Zone1b] AS ([Measures].[Zone1] - ([Measures].[Actual] * [Measures].[ShowZone1Marker])) / [Measures].[Zone3] * 100
this time the logic is:
the zone size subtract the actual value multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart.
Repeat the same for subsequent zones and then bring it all together in a select statement on your cube
Just in case I’ve explained all this as badly as I think I have, you can download the sample project and have a look yourselves: BulletCharts.zip (7.42 kb)
And the scale – errr OK in case you hadn’t already guessed, it’s an image on the column header – hey what more do you want?the moon on a stick? 😉 You can size the chart to fit the scale or create your own image. The image is also included in the sample project.
All criticism/suggestion for improvement welcome.
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar