Highlight values above and below the target in Power BI
using DAX and a Line and stacked column chart
This tutorial aims to create a line and stacked column chart to highlight values below and above the target using visual cues.
I’ve been showing this Power BI “trick” in a few of my workshops and sessions but realized I had never created the step-by-step tutorial. If you prefer watching over reading, you can find a video at the bottom of the page.
What you need to create the stacked column chart:
You don’t need a lot to create the stacked column chart in Power BI and highlight the values. Let’s break this down:
In my example, I have certain monthly and target values. This could, for example, be Sales & Sales Target or Sales TY, and the target would be at least Sales LY.
- Month
- Value
- Target
It would look like this example in a standard stacked column chart.
Next we create 2 DAX measures.
Adding 2 DAX measures to highlight different values
Value below target = IF([Current Value] < [Target],
[Current Value], [Target])
This DAX measure calculates the value that is below the target. If the current value is less than the target, it returns the current value. Otherwise, it returns the target value.
Value above target = IF([Current Value]> [Target],
[Current Value] – [Target], BLANK())
This DAX measure calculates the value above the target. If the current value is greater than the target, it returns the difference between the current value and the target. Otherwise, it returns a blank value.
Creating the stacked column chart to highlight the values
Everything you need to do now is add a line and stacked column chart to your Power BI canvas.
- Add the target as a Line Y-axis value (in my example, I choose a dotted line style).
- Add the 2 DAX measures as the Column Y-axis.
- Choose the colors you want to use to color everything above and below the target.
- Don’t forget to add Total labels to see the total value of your column chart.
Video: Highlight Values Above and Below Target in Power BI
To help you get a feel for the process, I created a video.
Recent Tutorials
Create your resume in Power BI combined with PowerPoint to instantly showcase your skills!