Forum Discussion
Power ParticipantHow do I create a bar chart with the column headers showing along the axis (no transposing)?
How do I create a bar chart with the column headers showing along the axis (no transposing in power query)?. I have created an example table below:
The bar chart should show the five items with the following labels, "A", "B","C" and "D". Each bar should display the sum of the values.
Any help would be greatly appreicated.
Disclaimer: The reason why I am trying to avoid transposing the table in power query is because I have created relationships between tables.
If you really can't transpose it (wondered if you could use a calculated table and effectively create a copy of the data) then you could do the following:
1) Create a disconected table that just contains the values you want on the x-axis:2) Write a measure like
BarValues = SWITCH ( SELECTEDVALUE ( 'Axis'[X-Axis] ), "A", SUM ( 'DataTable'[A] ), "B", SUM ( 'DataTable'[B] ), "C", SUM ( 'DataTable'[C] ), "D", SUM ( 'DataTable'[D] ) )Put the disconnected table column in axis and the measure in values:
16 Replies
- AlexisOlson
Super User
I wouldn't recommend transposing the table but rather unpivoting it. Power BI works nicely with unpivoted data.
This can indeed cause issues with relationships due to changes in cardinality, but it's likely that addressing these issues is easier and cleaner than the workarounds needed to chart pivoted data.
As bcdobbs requested, if you link to a demo file (ideally that includes relationships you need to preserve), then we can show you how to adjust it.
- bcdobbs
Community Champion
Good point on the language! I think I just assumed that what was meant.
HamidBeeI have created an example file which mimicks what I am working with:
https://www.mediafire.com/file/zbtkfxoltrei59z/Power_BI_Example.pbix/file
So just to summarize I am trying to create a bar chart where I have the column names running along the X axis (A, B, C etc.) and the values on the Y axis. I'm trying to do this without creating additional tables or new relationships. Maybe there is a way to create a measures which defines the row labels. I came across a video where someone did something similar:
https://www.youtube.com/watch?v=CRs2CnW7tVU
Thanks in advance
- AlexisOlson
The video you linked to does pretty much what bcdobbs initially suggested. It has a separate table for the measure names.
I'd recommend appending and unpivoting both of your tables into a single one like this:
Then creating your visual is just drag and drop.
- bcdobbs
If you really can't transpose it (wondered if you could use a calculated table and effectively create a copy of the data) then you could do the following:
1) Create a disconected table that just contains the values you want on the x-axis:2) Write a measure like
BarValues = SWITCH ( SELECTEDVALUE ( 'Axis'[X-Axis] ), "A", SUM ( 'DataTable'[A] ), "B", SUM ( 'DataTable'[B] ), "C", SUM ( 'DataTable'[C] ), "D", SUM ( 'DataTable'[D] ) )Put the disconnected table column in axis and the measure in values:
- HamidBee
Thank you for your response. I should have been added more to the description. I'd also like to do it without having to create a new table. The columns are in reality many and I'd like to create it in the fewest steps possible. I'm mindful of the fact that it may not be possible however I'm curious to know if someone out there has a method for doing this.
- bcdobbs
In that case I'm struggling to think of a good answer.
How dynamic does it need to be? Eg could you transpose the data in power query as a copy so you can maintain everything else in relationships but just use the transposed table for the graph?
Depends on what the data is in the columns and how far down the build you are but suspision is that you'd save time in the long run by structuring your data differently. Appreciate that sounds like it isn't an option though.
- HamidBee
I received your most reply however I'm actually starting to think this could work. Instead of creating a table could I just use a measure calculating the total for a column. I already have the measures for each column under my measure's table. Then use the example code that you've written. Would that be okay?. I've used the switch function but the selectedvalue function is new to me.
- HamidBee
I need to stop multitasking when I type, my messages are littered with typos.
