RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 810
- Office Version
- 365
- Platform
- Windows
Hi
I have a clustered column chart laid out in the following structure:
Asset on the X axis.
Cost on the Y axis
Stage is Legend
The idea of the chart is to filter by project using a slicer, and then all the Assets (components) of the project are across the X axis grouped by Stage. This way, we can compare costs of assets as the project moves through the 5 stages. Hopefully that makes sense.
In order to compare the total project cost at various stages, I want to include a secondary Y axis which is a sum of the Project ID for each particular stage. I have been utterly unsuccessful in doing this.
Here's how the main table "Database" is laid out.
There are multiple rows per Project ID. Each row is an asset, which has a cost in the Cost column. The Project ID can have between one to five stages, so the same asset might be repeated more than once but each is assigned to a different stage.
What I've tried doing is dragging the Cost into the second Y axis column, but that just puts a squiggly line on the chart which follows the bars. I then created a measure to Sum Up the Total Cost like so:
But this places a horizontal line on the chart which is a sum of both Stages. As an example, if Stage 1 is £130k and stage 2 is £150k then I want two horizontal lines on the chart, one at £130k and one at £150k. The above DAX creates one line at £280k.
The only way I've found this to work is to create 5 measures to sum each Project ID for each gateway, and then stack them together in the secondary Y-axis, like this:
etc from 1 through 5.
However, the caveat here is that it creates 5 additional items on the legend, whether the project has 1 stage or 5. But visually, the lines do go in the correct place on the chart.
I'm wondering if anyone can help? ChatGPT has been absolutely useless. Thank you.
I have a clustered column chart laid out in the following structure:
Asset on the X axis.
Cost on the Y axis
Stage is Legend
The idea of the chart is to filter by project using a slicer, and then all the Assets (components) of the project are across the X axis grouped by Stage. This way, we can compare costs of assets as the project moves through the 5 stages. Hopefully that makes sense.
In order to compare the total project cost at various stages, I want to include a secondary Y axis which is a sum of the Project ID for each particular stage. I have been utterly unsuccessful in doing this.
Here's how the main table "Database" is laid out.
There are multiple rows per Project ID. Each row is an asset, which has a cost in the Cost column. The Project ID can have between one to five stages, so the same asset might be repeated more than once but each is assigned to a different stage.
What I've tried doing is dragging the Cost into the second Y axis column, but that just puts a squiggly line on the chart which follows the bars. I then created a measure to Sum Up the Total Cost like so:
Power Query:
Total Cost = CALCULATE(
SUM('Database'[Cost]),
ALLEXCEPT('Database', 'Database'[Project ID], 'Database'[Stage])
)But this places a horizontal line on the chart which is a sum of both Stages. As an example, if Stage 1 is £130k and stage 2 is £150k then I want two horizontal lines on the chart, one at £130k and one at £150k. The above DAX creates one line at £280k.
The only way I've found this to work is to create 5 measures to sum each Project ID for each gateway, and then stack them together in the secondary Y-axis, like this:
Power Query:
Stage1Cost =
CALCULATE(
SUM('Database'[Cost]),
'Database'[Stage] = "Stage 1"
)etc from 1 through 5.
However, the caveat here is that it creates 5 additional items on the legend, whether the project has 1 stage or 5. But visually, the lines do go in the correct place on the chart.
I'm wondering if anyone can help? ChatGPT has been absolutely useless. Thank you.
