Sometimes it is interesting to see how your data has changed over time. Maybe you also place more value in your newer data rather than older data. An awesome way to visualize this is by showing the decay of your stale data like in the following gif. I received the inspiration for this method after Reid Havens over at Havens Consulting inquired on the possibility of such a visual. The methods shown in this blog post are mostly a culmination of my past articles and videos in a single, awesome demo.
Isn’t that so cool? The end product of this blog will be a scatter chart that conditionally formats the data points and fades the older points as we step through the data. There is an easy way and a hard (but rewarding) way and I will show each method in this blog post.
Easy Way
If you only need the data to be static, we can accomplish the above extremely easily. You simply need to set up a scatter chart and conditionally format based on a color scale. Start by adding a scatter chart and adding any two numerical columns in the X and Y wells and an ID column in the Details well. The details part is important because it allows the scatter chart to show all of your data points instead of aggregating them into a single value. I am using dummy survey data similar to the Gartner Magic Quadrant that ranks BI tools. For the purpose of this blog, just know that a high X value and high Y value are preferable. Here’s are the first several rows of my data:
Using this data, my basic scatter chart looks like this:
To wrap up the easy method, simply click on the formatting pane, then Data color, then click the 3 semi-hidden dots in the top right and select Conditional formatting. You can see how I set up the conditional formatting in the following picture. I am coloring by Color scale and using the sum of my Index column (ID Column) with a scale from light grey to black.
When I click Ok, the conditional formatting is applied and I can successfully visualize old data vs new data.
This looks great! But I also want to be able to step through my data like in the first gif. I have survey data from June 2017 through November 2019, so when I am viewing March 2018, I want the March 2018 point to be solid black while February 2018 is dark gray and June 2017 is almost white. This should update as more and more points are added. To achieve this, we have to build this in a more involved way. Stick with me. I promise it’s worth it.
Hard Way (But worth it)
A lot of my blogs and videos use the Play Axis Slicer to step through data points because it adds a whole other dimension to a Power BI report. Start by importing the Play Axis visual from the Custom Visuals Marketplace.
Now if you throw the ID column that we used in the details well of the Scatter Plot into the Play Axis Slicer visual, we can step through our data successfully. But as of right now, only one point will show at a time. We need a way to show all data points up to a point. We will create a measure that will show all data points up to whichever Index value is in our play axis slicer selection.
Before creating the measure, it is very important that we make a copy of our original Index column. Since my data set is small, I am simply going to copy my entire table of data. We are doing this because we can’t have a relationship between the scatter chart and the play axis slicer for us to show more than one data point at a time. This may be confusing at first, but just know that we need to have one Index column in our scatter chart and a separate Index column on a different table that drives our play axis slicer. To do this, I simply clicked on the Modelling tab, and New table, and entered:
Copy = Data
This simply creates a new table called “Copy” that is the exact same as my original table called “Data”. Use the Index column from out Copy table in the play axis slicer.
Next, we will build our measure with the following code:
Ability to Execute Measure =
VAR ScatterIndex = MAX(Data[Index])
VAR PlayAxisIndex = MAX(Copy[Index])
RETURN
IF(
ScatterIndex <= PlayAxisIndex,
SUM(Data[Ability to Execute]),
BLANK()
)
The logic compares the Index of the data point in the visual to the index in the current value of the play axis slicer. If the Index of the data point is less than or equal to the value in the slicer, show the sum of the “Ability to Execute” column which is our Y axis in the scatter chart, but if the data point Index is larger and the value in the slicer, show blank. A blank value will effectively hide the data point from the scatter chart entirely.
At this point, you can click the play button and you can see your data building over time!
The final step is to set up a conditional formatting scale, but this time with a custom measure instead of the built-in color scale. Create one more measure with the following code:
Color =
VAR ScatterIndex = MAX(Data[Index])
VAR PlayAxisIndex = MAX(Copy[Index])
VAR Transparency = DIVIDE(ScatterIndex,PlayAxisIndex) * 100
RETURN
IF(
ScatterIndex = PlayAxisIndex,
“#0000FF”,
“#0000FF” & FORMAT(Transparency,”##”)
)
I called the measure “Color” and the logic goes like this: Similar to the previous measure, we are storing both Index values as variables. We then create a third variable called “Transparency” that compared the scatter chart index to the play axis index and multiplies by 100. So imagine that our play axis has a value of 10, our scatter chart data point of Index 4 will have a transparency of 4/10*100 = 40. Next we return and IF statement that says “If the scatter chart index equals the play axis index, return the hex code for blue, otherwise return the hex code for blue and append our Transparency variable (formatted correctly). Basically, the hex code “#0000FF” will return solid blue while a value like “#0000FF40” will return blue with 60% transparency (100-40=60).
Finally, add conditional formatting to the scatter chart like we did before, but this time select Field value instead of Color scale. Select the new Color measure and click Ok!
And we’re done! Click the play button and visualize the fruits of your labor.
I also added the survey date to the visual to aid understanding. I hope you enjoyed walking through how to set this up! It truly is a cool way to visualize data decay or stale data in Power BI. If you enjoyed, make sure you subscribe to the BI Elite blog and YouTube channel!