Forum Discussion
Un-Summarize column, calculated rows?
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 |
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.1PaddedSize = MAX('Table'[Size]) * 1.1Use 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.
3 Replies
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.1PaddedSize = MAX('Table'[Size]) * 1.1Use 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.
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
