I recently came across a requirement to report the number of devices owned by customers, along with the age of each of the devices (how long it had been in service). The idea behind it was to be able to quickly see customers that had the majority of their devices within the replacement window (10+ years) and were going to need new ones soon.
The original requirement was to create a Matrix style report that listed the customers across the rows, and the Age (in years) of the devices across the columns. Eacch of the Value cells would show the number of devices that matched the age of the device for that customer.
Here’s an example of the original report:
As you can see, it’s not very easy to tell which customers have aging devices or what percent of their devices is within a certain age. A good way to improve this visual is to change it into a 100% Stacked bar chart and use color gradients to easily show the age breakout of the devices for each customer.
Creating the Stacked Bar visual
The first thing I did was to load the data into Power BI. You can reference the Excel file at the end of this post if you want to recreate the report on your end.
Once the data was loaded into my model, I used the “Age Year Sort” field to sort my “Device Age in Years” attribute so that I could show the aging from 0 – >15 years. From here I clicked the “100% Stacked bar chart” visual to create a new visual in my report.
I dragged the Customer attribute to the Axis, the Device Age in Years to Legend, and the Value to the Value box for the visual.
My chart now looked something like this:
Not much more useful than our original tabular report right? This is where our color gradient step will enhance our visual.
Creating a Color Gradient
I wanted to create a smooth gradient that ranged from bright green for devices that are less than 1 year old, to a bright red for devices that are 15 years or older. One way to go about achieving this is to start picking a range of 16 different colors and hope to pick the shades of green/yellow/red in the right order so that it diverges from green to red smoothly…not impossible, but probably a pretty painstaking process.
The other option is to leverage a nice little tool from the internet called RGB Color Gradient Maker (http://www.perbang.dk/rgbgradient). This handy tool lets you pick a starting and ending color, and how many steps you want in between those 2 colors (perfect for this scenario as I have 16 different steps).
For my starting color I chose #257A24 and for my ending color I chose #BF0003. I selected 16 steps and clicked Make Gradient. I chose to use the HSV Gradient (Inverse) which gives me a nice transition from green-yellow-orange-red:
Now that I’ve taken the guesswork out of my colors, I can simply copy and paste each of the color codes from above and apply them to each Year category in my Data Colors section:
My chart now looks like this:
Formatting and Final Touches
I can already see at glance which customers have an inventory of mostly older devices by simply looking at bars that are mostly red. I will add a few more details into my visuals, format the Title and Y-axis and call it done!
Add Data Labels
I turned the Data labels on, changed the Decimal places to 0, and the text size to 9. The data labels tell me what percentage of the total inventory for each customer fall within each Year bucket.
Format Axis & Title
Finally, I increased the minimum category width spacing on my Y-Axis to 25 so it didn’t look as condensed, and changed the font color to black. Lastly I update the chart title.
The end result is a color-coded bar chart that lets you see, at glance, the age of the devices for each customer and identify those that will need to be contacted shortly to refresh their aging inventory.
Other Enhancements
I can also look to add some slicers to my report so I can filter the number of customers that get shown on the chart. If I had a Customer Grouping or Category, I could add that to a List drop-down slicer. I can also add a simple Customer slicer that lets met search for customers and filter on specific ones.
Feel free to share your thoughts below!
