Forum Discussion

MaxSchrijen23's avatar
MaxSchrijen23
Icon for Helper I rankHelper I
4 years ago
Solved

Total column to clustered chart

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!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

  • radhey_rec's avatar
    radhey_rec
    Icon for Resolver II rankResolver II
    4 years ago

    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

    TeamStatusCountColor (this is calculated Field based on team)
    Team 1Active9#01b8aa
    Team 1WIP3#01b8aa
    Team 1Finished6#01b8aa
    Team 1Canceled2#01b8aa
    Team 2Active7#374649

    ...

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

     

     

    Thanks,

    -R