Forum Discussion
Power ParticipantHow do I create a bar chart with the column headers showing along the axis (no transposing)?
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:
Community ChampionIf 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:
HamidBeeThank 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
Above you a can see part of the actual table. I have two tables like this and one calendar table (acting as a facts table) which creates a relationship between the two. I'm creating various charts which show the total values over months. But I'd also like to create bar charts which show the totals for each columns. It may not be possible but it would be cool if there was a way. I sometimes work on large data sets and I'd do anything to avoid creating new tables.
- 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.
- bcdobbs
SELECTEDVALUE returns a value if there is a single value present in the filter context and defaults to blank otherwise. So when you use the disconnected table on the axis each point just has "one" selected value.
Not quite sure what you mean though. I don't think you can get the labels on the x-axis without the disconnected table if you go down that route. Although you could just drop the inidividual measures into Values and let the key label the bars.
Give it a go with a few columns and see what happens.