To label or not to label. Labelling of values in a line/bar chart in a dashboard is a contentious issue. Stakeholders coming from the world of static reports in Excel/pdf are used to seeing labels on charts and want the same functionality in Power BI. They do not want to put in the effort of hovering over a data point to get the labels (and more). So for them labelling is a must. However they are others who do not want labels on chart and are happy to explore the interactive functions of Power BI. And then there is the third group who want strategic labels on a chart (First, Last, High, Low). It seems impossible to accomodate everyone for a single chart. Or is it…
What if you provided a way for the end user to select the type of labels they want (from None to All and everything in between). They can select the lables which suits their requirements and even bookmark their selection for subsequent visits. Wouldnt that be great ? In this blog post we will discuss on how to do this in Power BI.
Lets start by creating a small multiple of sales across countries. It is filtered to one calendar year. I chose the small multiple visual as you can see how to implement the solution for multiple line charts at once. With a bit of formatting it looks like this
This chart has no labels. We will also create a new table which will be a disconnected table. It will look like this with the correct ordering.
We will then define three measures which will go into the chart above. The first measure is this
Sales Label Measure High =
VAR SelLabels =
SELECTEDVALUE ( Labels[Labels] )
VAR MaxValue =
MAXX (
ALL ( ‘Calendar'[ShortMonthName], ‘Calendar'[MonthNumberOfYear] ),
[Total Sales]
)
RETURN
SWITCH (
SelLabels,
“High and Low”, IF ( [Total Sales] = MaxValue, [Total Sales], BLANK () ),
“First, Last, High, Low”, IF ( [Total Sales] = MaxValue, [Total Sales], BLANK () ),
BLANK ()
)
This will return [Total Sales] when the user selects “High and Low” or “First, Last, High, Loww” in the selection and when the [Total Sales] is equal to the Maximum value in the range (Jan – Dec) else it gives blanks.
The next measure is the same as above except it checks the condition of [Total Sales] is equal to the minimum values in the range (Jan – Dec)
We could have combined these two measures but we want to apply a different colour to the markets for the High and Low data points which will see later. Hence we have defined them separately
Sales Label Measure Low =
VAR SelLabels =
SELECTEDVALUE ( Labels[Labels] )
VAR MinValue =
MINX (
ALL ( ‘Calendar'[ShortMonthName], ‘Calendar'[MonthNumberOfYear] ),
[Total Sales]
)
RETURN
SWITCH (
SelLabels,
“High and Low”, IF ( [Total Sales] = MinValue, [Total Sales], BLANK () ),
“First, Last, High, Low”, IF ( [Total Sales] = MinValue, [Total Sales], BLANK () ),
BLANK ()
)
We wil define one final measure
Sales Label Measure First Last =
VAR SelLabels =
SELECTEDVALUE ( Labels[Labels] )
VAR SelMonth =
SELECTEDVALUE ( ‘Calendar'[ShortMonthName] )
RETURN
SWITCH (
SelLabels,
“All”, [Total Sales],
“First and Last”,
IF ( SelMonth = “Jan” || SelMonth = “Dec”, [Total Sales], BLANK () ),
“First, Last, High, Low”,
IF ( SelMonth = “Jan” || SelMonth = “Dec”, [Total Sales], BLANK () ),
BLANK ()
)
This measure returns [Total Sales] when a user selects “All”. It also returns [Total Sales] when a user selects “First and Last”, “First, Last, High, Low” in the selection and when the selected month is either “Jan” or “Dec”
Now we place these measures in the small multiple visual so that it looks like this
Next is where the magic happens, in the formatting pane under Date Colours, Date Labels and Shapes. Here is a short video which goes through all the steps.
Finally we place the Labels colulm from the Labels table in a slicer and start interacting with the report