Cannot make a simple 100% stacked bar chart

[RockandGrohl]

Starting to think PowerBI isn't all it's cracked up to be.

I have a data table which contains a summary of my company's projects by ID.

The additional columns then summarise each of the category of costs.

Please see below table as an example:

Project ID	Total Costs	Stage 1	Stage 2	Stage 3	Stage 4
12345	£1,000,000	£100,000	£400,000,000	£250,000	£250,000
12242	£800,000	£300,000	£200,000	£100,000	£200,000

I'm simply trying to create a stacked bar chart which looks similar to this:

But no matter which fields I select for X-Axis, Y-axis, Legend, I cannot get any kind of bar chart to show up. When I drag the relevant columns over, nothing happens, it does not allow me to drop the stage columns in to display as fields.


The values in the data table columns are formatted as currency.

 

[RoryA]

Works fine here just adding the 4 stages as X-axis fields and the project ID as Y-axis.

 

[RockandGrohl]

Works fine here just adding the 4 stages as X-axis fields and the project ID as Y-axis.

When I put the Y-axis as "Project ID", I am unable to drag anything in to the X-axis.


Let me explain a bit more as I may have missed something out.

My main table is called Database. It contains thousands of rows, so for example probably 40 rows for project 12345, and 32 rows for project 12242, etc.

There's a column called Cost Heading and this is what I'm using to categorise the four stages. But there's more than 4 types of values, so Stage 1 = Stage 1, Stage 3 = Stage 3, Stage 4 = Stage 4, but everything else is categorised as "Stage 2"


Because of this, I've created a new table - CostBreakdown

This table has one line per project ID, and the costs broken down using calculated columns, exactly as in the example table above.

However, I am totally unable to drag any fields into the X-axis. Whether it's a calculated column, or a measure, I drag & drop and nothing happens and the field input stays blank.

Is there any way I can screen share to you, would rather not paste my project publicly on the Internet. Thanks.

 

[RoryA]

Is there any way I can screen share to you

No, there isn't.

I just copied and pasted your data from above into a table, and then loaded it into PBI and dropped the fields into a visual, with no problems. Can you not produce an anonymised sample to share?

 

[RockandGrohl]

No, there isn't.

I just copied and pasted your data from above into a table, and then loaded it into PBI and dropped the fields into a visual, with no problems. Can you not produce an anonymised sample to share?

I'm not sure why my data is different.

It's on a new table, as above.

Can I privately send you the file after I've anonymised it?

 

[RoryA]

Why does it need to be private if it's been anonymised?

 

[RockandGrohl]

Why does it need to be private if it's been anonymised?

Haha, I'll DM you the reason.

 

[RockandGrohl]

I have this, how do I now sort by the legend? I'd like the dark blue series to show the largest percentage first, and then roll down through to the smallest percentages last. Thank you.

 

[RoryA]

Change the order of the fields on the axis?

 

[RockandGrohl]

Change the order of the fields on the axis?

The only way I could get it to work is as follows:

Y-axis = Project ID
X-axis = Stage Cost (DAX - Stage Cost = SUMX(Database,Database[Cost (Inflation)])
Legend = "Construction Stages" grouped values, where I manually grouped the items I require into each Group.

Now that the chart is properly showing all my stages in order (Stage 1, Stage 2, Stage 3, Stage 4), I just need it to sort the values within Stage 1 from largest to smallest. That's my next challenge.