Make Power BI Column chart Data labels great! (not again, as I don’t think they were ever great 😂)
Making enough room for the Y-axis on the column chart so the data labels look good is one of the things in Power BI that drives me nuts.
The chart should just have enough room on the Y-axis for the data labels to sit comfortably outside of the columns, never having to go inside the columns or sometimes sit halfway out of the columns, which does happen.
Especially if you’re using field parameters and you’re switching between different values in the column chart, the data labels can look good sometimes and then switch to another measure and they look really bad.
As you can see in the example in the picture, we have data labels that are sometimes inside of the column and sometimes outside of the column. But you might say, “Oh, we can just always put them inside of the column.”
Generally, I find that this doesn’t look very clean and doesn’t work if you have one month that is a very low number. Then the data label itself will just completely disappear.
You might say, “Well, we can just use visual calculations or measures and then set the maximum y-axis. That will always ensure that there is enough space for the data labels to sit above the columns.”
The issue I had is when you have a drill-down. For example, if you have months (which is the sum of the months) but then you want to drill down to a day level within the month. The daily transactions might be much smaller than the monthly transactions, and the y-axis does not adjust for a drill-down.
If you have a larger Y-axis max, it will stay there, and your columns will look very small when you drill down on a month.
Never fear, I have a solution.
Error bars.
Create one measure as such:
Error Bars = [Y axis measure] * 1.2
Put that measure in the upper bound of an error bar, turn the “bar” on & set the colour of the error bar to be the same as the background of the chart. Also ensure that the error bar tooltips & markers are off. The error bar is now there but invisible to the report viewer.
And there you have it. The y-axis max will dynamically adjust no matter the current data visible within the chart. Leaving plenty of room for your data labels to sit outside of the columns.
This solution responds to drill-downs also, always setting the y axis to be x 1.2 of the amount that is currently displayed in the chart. 1.2 is, of course, an arbitrary number. You could set it to more or less.
The PBIX file is on my GitHub in the link below if you’d like to see exactly how I did it.
https://lnkd.in/g2n2adJt
PBI Error Bars, The Ultimate Column Chart Data Label Hack
