Total column to clustered chart

[MaxSchrijen23]

I am trying to create a column chart that contains the separate values for my series and one column that contains the total value of all my series summed together. 

My data is a project table containing the project name, status and a team it belongs to. Now I want to create a column chart that counts the amount of projects per status per team, but at the same time also show a column with the total of all the projects for this status. It should look like this:

Does anybody know how to do this?

Thanks in advance!

[v-stephen-msft · Accepted solution]

Hi @MaxSchrijen23 ,

I think you are going to add a total column to the clustered column chart.

Here's my solution.

Sample data

1.Create a table as follows(Enter data). If you have a lot of values and it is not convenient to enter them directly, please let me know.

There's no relationship.

2.Create a measure.

Measure =
IF (
    MAX ( 'Table (2)'[ID] ) = "Total",
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Year] = MAX ( 'Table'[Year] ) )
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', [ID] = MAX ( 'Table (2)'[ID] ) )
    )
)

3.Results.

Best Regards,

Stephen Tao

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

[MaxSchrijen23]

Thanks for your answer, this helped!

[mahoneypat]

You may need to use the Charticulator visual for this one, but should be doable (and free).

(15) Austin Power BI User Group - Charticulator 101 - YouTube

But even easier would be to use a line and clustered column chart (one of the native visuals) and put your measure also as the line values too.

Pat

[radhey_rec]

Hi @MaxSchrijen23 ,

I am not sure you can do that in Power BI. Please check.

In case you fail to create it with the native power bi visuals, you can try custom visual that I am able to create with https://pbivizedit.com visual generation service. You can download pbix and custom visual file from the below link to verify if this satisfies you need:

https://pbivizedit.com/gallery/2255098

Given the data

Team	Status	Count	Color (this is calculated Field based on team)
Team 1	Active	9	#01b8aa
Team 1	WIP	3	#01b8aa
Team 1	Finished	6	#01b8aa
Team 1	Canceled	2	#01b8aa
Team 2	Active	7	#374649

...

with the above visual, I am able to generate following chart:

Thanks,

-R