In my article last week, I explored how to compare a goal built off last year’s revenue compared to this year’s actuals. Some of you might be thinking that system worked, but it felt cluttered. I would agree with you. I like using cluster charts in the right scenario, but I think we can do better. A couple of weeks ago, I did an article on why I love using conditional formatting to highlight important values. You also might remember that it was focused on using the table or matrix visual.

A little known fact is that you can leverage conditional formatting in other visuals. Imagine using conditional formatting to quickly identify which months we hit or missed our goal. I thought it would be great to dig into some options to really make your column charts pop!

Using Some Conditional Formatting Magic

A few weeks ago, I wrote an article on how to do perform field based conditional formatting. The same principle applies to building out our formatting in our charts. I start by building a measure that compares current year revenue to our goal.

CY Revenue Color = 
    SWITCH(TRUE(),
        [CY Revenue] >= [CY Goal], "Green", 
        [CY Revenue] >= ([CY Goal] * .90), "Yellow",
        "Red")

The formatting will show green if revenue exceeds our goal. We then identify any months that reached 90% of their goal in yellow to highlight that they were close, but did not quite make it. Then any months where we missed our goal, it now shows in red.

Now we need to apply the formatting to our chart. After selecting the visual, we need to go to the visual properties pane. Then you need to expand the columns section and click on the “fx” button. From there, just select the field value formatting style and select the CY Revenue Color measure we just created.

Applying our measure for conditional formatting.

The result is a chart that looks like this:

Field based conditional formatting on a column chart

It was a few extra steps, but it makes it clear we started the year off strong, but have some work to do to get back on track. Amazing what a little color can do!

This Is Great, But…..

I know what you are about to say – “I love the color, but it is hard to see how this compares to the actual goal. Can we do something about it?” Of course we can!

When traditional conditional formatting lets off, we can build our own with a series of measures. Using a stacked column chart and a few measures, we can show our revenue versus the goal to make it easier to see. We need to build four measures to make it easy to track against our goals: Revenue up to our goal, revenue that exceeds our goal, revenue short of our goal, and revenue left to meet our goal. Yes, it seems complicated, but just wait until you see the final product.

Our first measure will calculate the CY Revenue that is up to our designated goal. If we exceed the goal, we will just return the CY Goal instead and represent the remainder of the revenue in another measure.

CY Revenue to Goal = 
IF([CY Revenue] >= [CY Goal], [CY Goal], [CY Revenue])

Next, we want to show anything that is over our goal in another measure. Using a similar formula, we will calculate the difference of CY Revenue and the CY Goal. Note there is no else statement as we are only interested in revenue over our goal.

CY Revenue Over Goal = 
IF([CY Revenue] >= [CY Goal], [CY Revenue] - [CY Goal])

Next, we want to calculate the amount we missed our goal by taking by looking at months were we missed our revenue goal. 

CY Revenue Short of Goal = 
IF([CY Revenue] < [CY Goal] && 
     MAX(SalesAggregated[Month]) <= EOMONTH(TODAY(), -1), 
     [CY Goal] - [CY Revenue])

We don’t want our chart to show missed revenue for months that have not happened yet. We also don’t want to show missed revenue for our current month but rather as revenue to go. By using EOMONTH(), I am able to take the end of the month that we are in today and subtract a month so it will only summarize data before my current month.

Now doing something similar, I can calculate the revenue that still has to happen before the end of my current month. We will essentially do the same measure as above but looking forward.

CY Revenue to Go = 
IF(MAX(SalesAggregated[Month]) > EOMONTH(TODAY(), -1), 
     [CY Goal] - [CY Revenue])

And with a little color assigned in the, we can make our chart pop!

Homemade conditional formatting using some measures and custom colors

Which Method When?

Both methods work really well for providing conditional formatting. But one obviously takes a lot more effort to create. And beyond that, I need to make sure my DAX is clean and doesn’t slow down my report.

Why would I go down this route? I find that sometimes you have a stakeholder who really likes to get into the minutiae of the data and wants to be able to accurately see their plan versus actuals. This might have been a little more complicated than required, but I wanted to show you how granular you can get.

From my experience, most people do not want to get this granular with their reports. But in case you run into someone who really wants some additional detail, you now know how to make it happen.

Have you run into something like this before? Have you been asked to add some formatting to your column charts? Are you going to start doing it? Tell me in the comments below!