Introduction
This week we are continuing our design theme by working through a custom formatting hack for line charts. Conditional formatting in Power BI can be tricky at times, especially when trying to format different chart types.
If you are familiar with conditionally formatting a line chart, you’ll know that in the Data Colors section of the Formatting pane you don’t have the very familiar [fx] button that allows you to control the conditional formatting logic. If you click the Show All toggle, you can color each line point separately, but this is really limiting when trying to make your coloring dynamic.
In this challenge this week we’ll show you how to work around that limitation to create a line chart where the individual points are subject to the standard conditional formatting logic. You can see an example of the end result, below.
Requirements
- Download the PBIX file from the link, here
- Create a small multiple chart plotting the [Games Above .500] field for each [Year] with the small multiple being the [Franchise ID]
- You can either start with a small multiple line chart here and see if you can find another work around, OR you can make this small multiple line chart. Either way you will have to make the chart a bar chart being proceeding
- Make your small multiple chart a bar chart if it’s not already
- Create logic to color your bars using the following logic
- W-L% > 55% then Blue
- W-L% >= 45% then Gray
- W-L% < 45% then Red
- Select the small multiple bar chart that you’ve created and now click in the Visualizations pane on the Line Chart. This will switch the existing report to a Line Chart with the conditional formatting included.
- Note: if you toggle on Show All in the Data Colors section you will have the ability to manually override your conditional formatting logic. Sometimes this can be useful, but it could also cause your conditional formatting to show up incorrectly in the future, so just be careful when doing that.
Dataset
This week you can find the PBIX file here. The data model should already be complete, all you need to do is complete the visual per the requirements above.