cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smore
Frequent Visitor

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.

NameSubGroupSize
A3100
A250
A125
B2100
B1200

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:

NameSubGroupSize
A4null
A3

100

A250
A125
B3null
B2100
B1200

 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.

NameSubgroupValueNeeded
A4
B3


However, I do not know how to un-summarize or expand this table to the result I want as seen below:

NameSubGroupSize
A4null
A3

100

A250
A125
B3null
B2100
B1200
1 ACCEPTED SOLUTION
KNP
Super User
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)

 

KNP_0-1633975071974.png

 

(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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

3 REPLIES 3
KNP
Super User
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)

 

KNP_0-1633975071974.png

 

(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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

smore
Frequent Visitor

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!

PaulDBrown
Super User
Super User

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.