Dynamic Chart Labels with Calculation Groups
A really useful application for calculation groups is giving users the ability to control labels and number formats in charts and reports. In a previous post, I showed how you can give users the ability to turn chart labels off and on with calculation groups by using the blank value UNICHAR(8203). In this post there are some examples of some more creative uses related to highlighting specific values with labels. Combined with conditional formating of chart colours, the end result is pretty cool I think.
The key to implementing this solution and similar examples is utilising the format string expression in calculation items and controlling what values are visible by using UNICHAR(8203) to create a blank format string. Labels like “Highest” and “Lowest” are also added straight into the format string expression.
The implementation is pretty straightforward, to start I created a “Labels” Calculation Group in Tabular Editor with the following calculation items:
The normal “Expression” is just set as SELECTEDMEASURE() for all the calculation items allowing the value of whatever measure used in the chart to be unaffected by the calculation group. The Format String Expression is where all the fun happens. Here is the “Highest & Lowest Values” Format String Expression:
The VisibleDates variable grabs all the date values in the current filter context in the chart. You could easily use a different axis like Month Year by altering the code to suit
Highest and Lowest variables iterate this and find the highest and lowest values in the chart. The SWITCH statement then checks if the chart value matches the Highest or Lowest values and if it does it appends “Highest : “ or “Lowest : “ to the format string. If the chart value does not match the Highest or Lowest variables the UNICHAR(8203) is used as the format string which has the effect of displaying no label.
The Top Third and Bottom Third options use a similar pattern which dynamically labels all values falling within the top 33% or bottom 33%:
Some labels are missing in the above charts because I have made the text large for readability but all labels are actually there:
For the “All Values” option both the Expression and Format String Expression are left as SELECTEDMEASURE() and SELECTEDMEASUREFORMATSTRING(), basically leaving the chart untouched.
I added in “Look at me” and “Oh No!” options for a bit of fun just to show that you can use a format string to comment on a chart value not just change the basic format:
Once you have the basic idea, there are endless variations and creative applications. You could add an alternative measure to a calculation item instead of SELECTEDMEASURE() like [Margin] and highlight the highest margin month switching in the actual margin value into the chart. It also works great on different types of charts:
Combining it with conditional formatting is the key to making it effective. Unfortunately I havent found a way to apply conditional formatting to SELECTEDMEASURE() so you need to reference the actual measure used in the chart. Here is the measure I used to apply the conditional formatting which pretty much just replicates the logic in the calculation items but applies a hex colour.
Thats all there is to it pretty much. Get in touch if you have any questions or want a sample file :)
Cheers
