Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all!
Is there a solution to embed the Y-Axis Textes into the bars of the bar chart?
Example how it looks like normally and how I want it to look:
Solved! Go to Solution.
Hi there @Sean
Good to hear from you 🙂
My original solution had some limitations, which you have highlighted.
1. The reason the numerical data labels outside the bars disappear is that the bars I had created for them were a fixed size of 1, and data labels can't spill when 2+ bars are stacked. I'm not sure there's a great solution to this, but we could make the bar size scale with the other bars, e.g.
Zero Measure Top =
-- Used to give space to plot data label outside end of bar
VAR ScaleFactor = 0.2
VAR MaxPositiveValue =
CALCULATE (
MAXX (
VALUES ( Dummy[Label] ),
[Value Sum]
),
ALLSELECTED ()
)
VAR BarSize =
MaxPositiveValue * ScaleFactor
VAR ValueSum =
[Value Sum]
RETURN
IF (
NOT ISBLANK ( ValueSum ),
BarSize * SIGN ( ValueSum )
)
Also, to make negative labels (outside the bar) behave, I had to change the format string expression to:
VAR ValueSum = [Value Sum]
RETURN
SWITCH (
TRUE (),
ISSELECTEDMEASURE ( [Zero Measure Base] ), SELECTEDMEASUREFORMATSTRING (),
-- Number format for axis
ISSELECTEDMEASURE ( [Zero Measure Top] ),
VAR FormatInitial =
FORMAT ( ValueSum, "0.0" )
-- Correct for extra negative sign that shows up in data label
-- Maybe a better way of doing this!
VAR FormatFixed =
IF (
ValueSum < 0,
RIGHT ( FormatInitial, LEN ( FormatInitial ) - 1 ),
FormatInitial
)
RETURN
"""" & FormatFixed & """",
-- Number format to appear outside bars
"""" & SELECTEDVALUE ( 'Dummy'[Label] ) & """" & ";"""
& SELECTEDVALUE ( 'Dummy'[Label] ) & """" -- Label value surrounded by quotes
)
2. The reason the Text labels disappear is similar, since data labels can't overflow. I imagine that some tweaking of format string expressions could get them showing up against another measure in an appropriate place - I will leave to you or anyone else as an exercise 🙂
I've attached my file with the updates mentioned above.
All the best,
Owen
Hi @lynnsop
Here's one idea: Create a Calculation Group with a Calculation Item whose number format is the Label text.
I've attached a sample PBIX demonstrating this.
Initially, I created a Calculation Group with a single Calculation Item:
SELECTEDMEASURE()
"""" & SELECTEDVALUE ( 'Dummy'[Label] ) & """
Then I applied this single Calculation Item as a filter on the visual, and set the Position for the data labels to "Inside base".
After that, I played around with some additional measures [Zero Measure Base] and [Zero Measure Top] to ensure the horizontal axis number format looks sensible and allow for numerical data labels outside the bars, so I changed the Format String Expression to:
SWITCH (
TRUE (),
ISSELECTEDMEASURE ( [Zero Measure Base] ),
SELECTEDMEASUREFORMATSTRING (), -- Number format for axis
ISSELECTEDMEASURE ( [Zero Measure Top] ),
"""" & FORMAT ( [Value Sum], "0.0" ) & """", -- Number format to appear outside bars
"""" & SELECTEDVALUE ( 'Dummy'[Label] ) & """" -- Label value surrounded by quotes
)
Tooltips might need tweaking (e.g. report page tooltips) or switching off.
Hopefully this is of some use - I would be interested in whether this works for your report.
Regards,
Owen
@OwenAuger
Ok so actually I have no clue what you are doing, so I just downloaded your PowerBi Report, copy pasted your chart and swapped out your data/columns with mine.
It kinda looks good - though not yet correct:
Instead of showing the Barnames (in our example this would be "text xy") it shows the seperate counts.
The Barnames are not shown at all.
Hi @lynnsop
To make this work, the key requirement is to create a Calculation Group - I apologise that I didn't go through all the steps required to do that.
The reason Calculation Groups are useful here is that one of their features is over-riding number formats, which we want to do in order to change the data labels. Unfortunately Power BI doesn't natively allow measure-driven data labels, so this is a technique to get around that.
In your example, we can override number formats with the text value of the Barnames. When the Calculation Item is applied as a filter on the visual, the data labels will use the number format defined in the Calculation Item, rather than the usual number format.
If you haven't encountered Calculation Groups before, I recommend this article, or there are various other articles online.
Here is a rough guide to the steps I followed to create the visual in my PBIX file:
Line 7 is the one that sets the number format to the value of the text field you want to show on the visual, in my case 'Dummy'[Label]. This should be replaced with the relevan field in your data model. The rest of the formula handles some special measures I created to go above & below the main data bar in the visual, but you could omit these if you just want a simple visual.
Hopefully that helps!
Kind regards,
Owen
Hi @OwenAuger !
Great solution as always! 🙂
If we modify the sample data a bit
Text1 | -6 |
Text2 | 23 |
Text3 | 317 |
Text4 | 795 |
Text5 | 450 |
Text6 | 356 |
Text7 | 235 |
the first 2 values no longer show their data labels and ALL values disappear? 🤔
It seems if the numbers are not close together the Zero Top Measure Values are no longer displayed? Any suggestions or ideas?
Thanks! 🙂
Sean
Hi there @Sean
Good to hear from you 🙂
My original solution had some limitations, which you have highlighted.
1. The reason the numerical data labels outside the bars disappear is that the bars I had created for them were a fixed size of 1, and data labels can't spill when 2+ bars are stacked. I'm not sure there's a great solution to this, but we could make the bar size scale with the other bars, e.g.
Zero Measure Top =
-- Used to give space to plot data label outside end of bar
VAR ScaleFactor = 0.2
VAR MaxPositiveValue =
CALCULATE (
MAXX (
VALUES ( Dummy[Label] ),
[Value Sum]
),
ALLSELECTED ()
)
VAR BarSize =
MaxPositiveValue * ScaleFactor
VAR ValueSum =
[Value Sum]
RETURN
IF (
NOT ISBLANK ( ValueSum ),
BarSize * SIGN ( ValueSum )
)
Also, to make negative labels (outside the bar) behave, I had to change the format string expression to:
VAR ValueSum = [Value Sum]
RETURN
SWITCH (
TRUE (),
ISSELECTEDMEASURE ( [Zero Measure Base] ), SELECTEDMEASUREFORMATSTRING (),
-- Number format for axis
ISSELECTEDMEASURE ( [Zero Measure Top] ),
VAR FormatInitial =
FORMAT ( ValueSum, "0.0" )
-- Correct for extra negative sign that shows up in data label
-- Maybe a better way of doing this!
VAR FormatFixed =
IF (
ValueSum < 0,
RIGHT ( FormatInitial, LEN ( FormatInitial ) - 1 ),
FormatInitial
)
RETURN
"""" & FormatFixed & """",
-- Number format to appear outside bars
"""" & SELECTEDVALUE ( 'Dummy'[Label] ) & """" & ";"""
& SELECTEDVALUE ( 'Dummy'[Label] ) & """" -- Label value surrounded by quotes
)
2. The reason the Text labels disappear is similar, since data labels can't overflow. I imagine that some tweaking of format string expressions could get them showing up against another measure in an appropriate place - I will leave to you or anyone else as an exercise 🙂
I've attached my file with the updates mentioned above.
All the best,
Owen
@lynnsop , I doubt the option is there for standard visual
Check some custom visual - https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |