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.
Visual bar charts with 'Auto' Y axis cut the upper data bar in half. I am trying to add padding to the data by adding a null value in the table so it pads the visuals.
Name | SubGroup | Size |
A | 3 | 100 |
A | 2 | 50 |
A | 1 | 25 |
B | 2 | 100 |
B | 1 | 200 |
Here is an example of my table. If I plot [Size] on X axis and [SubGroup] on Y axis with 'Auto' setting, the visual will cut the bar or bubble for [A], [SubGroup] 3 in half. I need to add padding to the visual. As a work around i figured I could do the following:
Name | SubGroup | Size |
A | 4 | null |
A | 3 | 100 |
A | 2 | 50 |
A | 1 | 25 |
B | 3 | null |
B | 2 | 100 |
B | 1 | 200 |
This way when plotting, the Y axis now auto adjusts to 4 and shows no data plotted but the data in [A] [SubGroup] 3 shows completely. The reason for needing padding is that if I filter for B, the Y axis needs to update accordingly as the [SubGroup] sizes are variable.
I created a calculated Table2 which summarizes Table1[Name], finds the Max [SubGroup] and adds 1. (SubgroupValueNeeded) column.
Name | SubgroupValueNeeded |
A | 4 |
B | 3 |
However, I do not know how to un-summarize or expand this table to the result I want as seen below:
Name | SubGroup | Size |
A | 4 | null |
A | 3 | 100 |
A | 2 | 50 |
A | 1 | 25 |
B | 3 | null |
B | 2 | 100 |
B | 1 | 200 |
Solved! Go to Solution.
I would take a slightly different approach to this.
Create two measures one for padding the sub-group and one for padding the size.
PaddedSubGroup = MAX('Table'[SubGroup]) * 1.1
PaddedSize = MAX('Table'[Size]) * 1.1
Use these measures in the respective axis 'End' fields to create the padding you need. That way you don't need to create any dummy data. Make sure to set the 'Start' value to zero also.
(see attached PBIX file)
(I may be misunderstanding your issue. If so, can you post an image of your visual?)
Hope this helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
I would take a slightly different approach to this.
Create two measures one for padding the sub-group and one for padding the size.
PaddedSubGroup = MAX('Table'[SubGroup]) * 1.1
PaddedSize = MAX('Table'[Size]) * 1.1
Use these measures in the respective axis 'End' fields to create the padding you need. That way you don't need to create any dummy data. Make sure to set the 'Start' value to zero also.
(see attached PBIX file)
(I may be misunderstanding your issue. If so, can you post an image of your visual?)
Hope this helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
This was the original approach I wanted to take but did not have function capability for the Y or X axis. Seeing your screenshots made me realize that I have to update Power BI Desktop. I now have the FX capability for Y and X axis and your measure works perfectly. Thank you for the help!
Make sure both the Subgroup and Size columns are not summarized in both the table and the field bucket of the visual. You might also need to select the option for "Show items with no data" in the field bucket for the visual
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |