When displaying value changes over time, the past values, the current values and the planned values are often in a different color. One approach is to create several measures for this purpose. Today I want to show you that the number of measures can be reduced by means of color saturation.
One word before we start – all pictures are from the German version of Power BI Desktop.
Use of multiple Measures
Showing the three mentioned values can be achieved in Power BI by using multiple measures, for example. In the example below, he used three, very simple, measures:
- Umsatz Vorjahre = historical values (displayed in black)
- Umsatz Plan aktuelles Jahr = Budget current year (displayed in blue)
- Umsatz Plan = Planned values (displayed in purple)
There is no objection to the chosen approach, but it can become confusing in larger models with dozens of measures. My colleague, Cedric, has therefore sought a different way and has come up with a new solution.
Same result with only one measure
His approach is the use of color saturation. In the following picture you see the same result, achieved with only one measure and the relative year.
Below is the formula for the corresponding measure:
even simpler is almost impossible – OK, no need to use Calculate 😉
The measure is simple and does not require any filters. In order to achieve the same graphic result as with three measures, Cedric set the color saturation depending on the relative year.
insert relative year
In the diagram settings we place the relative year from the date dimension aka calendar into the field “color saturation”.
Next, go to the menu item “Format” and select “Data colors”. We activate the option “Diverging” and can define the desired colors.
The procedure presented only works when using one single measure. If there are several measures in the same chart, color saturation cannot be used.
With only a single measure and the use of color saturation, we can easily and efficiently display values as a function of the relative year.
Best thanks to Cedric for the great idea!
Dear readers, I write my articles for you. It is my ambition to provide you with quality content. Please take a minute and give me a rating or write a comment below. Thank you very much.