I recently had a client ask me if we could set up a bar chart showing revenue by a certain category with data labels. Of course, this is no problem. Then he asked me if those data labels could show the % of the total revenue instead of simply the revenue for the particular bar. With this challenge in mind, I set off to find a solution. I’m happy to say that this is 100% possible for both bar and line charts and now I want to share this method with you 😊

There are a lot of formatting options that need to be applied to get this looking completely perfect. In this blog post, I will outline all of the steps while keeping the screenshots to a minimum. Take a look at the YouTube video above for a visual walkthrough of how to set this up.

Bar Chart:

Here are all of the steps to achieve data labels as a percent in bullet form:

  1. Create a Line and clustered column chart. I believe either combo chart will work, but I only tested with this one.
  2. Add a field to the shared axis.
  3. Add a metric to both the column values and line values.
  4. Click the dropdown on the metric in the line values and select Show value as -> Percent of grand total.
  5. In the formatting pane, under Y axis, turn on Align zeros and change the font color of the secondary axis to white.
  6. Turn on Data labels.
  7. Scroll to the bottom of the Data labels category until you see Customize series. Turn that on. Select your metric in the drop down and turn Show to off. Select the metric that says %GT [metric] and ensure that that stays on. Also, change the position to under and make the font size larger if desired.
  8. Remove the line from the visual by navigating to Shapes and setting the Stroke width to 0.
  9. Finally, turn the legend off to remove any trace of the original line.

 

And that’s it! See the picture below for how my bar chart with % data labels turned out!

Line Chart:

The steps to set up a line chart are roughly the same, with a few differences.

  1. Create a Line and clustered column chart.
  2. Add a field to the shared axis.
  3. Add a metric to both the column values and line values.
  4. Click the dropdown on the metric in the column values and select Show value as -> Percent of grand total.
  5. In the formatting pane, under Y axis, turn on Align zeros.
  6. In the primary Y axis settings, set the position to right. Remove the axis title and change the primary axis font color to white.
  7. Turn on Data labels.
  8. Scroll to the bottom of the Data labels category until you see Customize series. Turn that on. Select your metric in the drop down and turn Show to off. Select the metric that says %GT [metric] and ensure that that stays on.
  9. Create a measure with the following code: TransparentColor = “#FFFFFF00”
  10. Under Data colors, change the “Default color” to use conditional formatting by clicking on the three dots that appear when hovering to the right, choose Format by field value and select our “TransparentColor” measure. Now the bars will be transparent.
  11. Finally, turn the legend off to remove any trace of the original bars.

 

And there we go! We have a fully working line chart with data labels showing the % instead of the actual data value!

If you liked this trick, make sure you subscribe to the BI Elite blog for more tutorials just like this one! Also make sure to check out the accompanying video shown above for even more information on how to set this up. Happy learning!