How do I create a bar chart with the column headers showing along the axis (no transposing)?

[HamidBee]

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. 

[AlexisOlson]

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.

[HamidBee]

I 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.

[HamidBee]

It was a long shot I was really hoping that I wouldn't need to unpivot it but I guess there really a way to avoid unpivotting the table. Thanks anyway. In future I'll just work with the table unpivotted as it makes more sense.

Out of curiosity how did you upload the power BI file? I uploaded mine on MediaFire and shared the link. 

[Anonymous]

Hi @HamidBee ,

I aggree with AlexisOlson . You couldn't add multiple measures in your bar chart. However , you need to add a X axis in your visual to achieve your goal. The best workaround for you is to unpivot your table. If you don't want to do this, you need to create a Dimtable with all column headers A,B,C and so on. You need to add the column from this Dimtable to Axis field in your bar chart. Then create a measure for it.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You can avoid unpivoting but only at the cost of needing extra dimension tables, switching measures, and more headaches in general. Unpivotting data is usually the better way to go.

I have a few special privileges being a Super User and attaching a file is one of them.

[bcdobbs]

Good point on the language! I think I just assumed that what was meant.

[bcdobbs · Accepted solution]

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]

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. 

[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.

[HamidBee]

I need to stop multitasking when I type, my messages are littered with typos. 

[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]

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. 

[bcdobbs]

In the columns Allowed, Mixed, No Context are you counting the same thing? Just with different "flags"?
If so those columns headings would be best as values in a dimension table and then your fact table can be transposed.
A simple measure of SUM (Table[ValueColumn] ) would then work in conjunction with the dimenion. Any individual sum can be found by applying visual level filters or by explicit dax eg:

Total Allowed = 
CALCULATE (
SUM (Table[ValuesColumn],
DimCategory[Category] = "Allowed"
)

If you wanted to mock up a simple demo with some fake data in form you have now I'd be happy to resturcture it and show what I mean. Even with massive datasets Power Bi will cope with it easier than the form it is currently in.

[HamidBee]

Hi, 

I shared the file above. Please let me know if you require any more information.

Thanks for your help.