Power BI – reduce the number of measures using color saturation

This post is also available in: de

Hits: 357

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) 
Using multiple Measures
Using multiple Measures
The measures are calculated using the relative year, which is defined in the

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.

Same result with only one MeasureSame result with only one measure

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

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.

settings for color saturation
settings for color saturation

Limitations

The procedure presented only works when using one single measure. If there are several measures in the same chart, color saturation cannot be used.

Conclusion

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!

You can learn more about Power BI on my German Power BI Tube channel. You can find more articles about Power Query in the Power Query Online book .

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.

Happy Querying!

Post provides benefit
1
Post is understandable
2
Appropriate post length
2
Average
  Loading, please wait yasr-loader

Leave a Reply