Total of population in 100% bar chart

[Anonymous]

Is there a way to show part and whole in 100% bar charts? My example is that I have a total number of employees, and then I have the number of them that left. I want to show the total as the top bar (with gender or race as the legend) and then the departures as the second bar (also with either gender or race as the legend). I can't wrap my head around how to structure this to get it to work.

EmpID	Active Jan 1	Departure
1	Active	no
2	Active	no
3	Active	yes
4	no	no
5	Active	no
6	Active	yes
7	Active	yes
8	Active	no
9	no	no

The top bar would be 7, the bottom would be 3. They would both be 100% bar charts.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your description, I did a test, the table for testing is as follows.

1. Create a new calculated table.

Test1 =

var total_Measure = CALCULATE(COUNTROWS('CASE1'),'CASE1'[Active Jan 1]="Active")

var Departure_Measure = CALCULATE(COUNTROWS('CASE1'),'CASE1'[Departure]="yes")

return

ADDCOLUMNS(

    ALLSELECTED(CASE1[gender]),

            "departure_table",'CASE1'[Departure_Measure],

            "total",'CASE1'[total_Measure]

)

1. Unpivot “departure_table” and “total” columns.

Test2 =

UNION(

        SELECTCOLUMNS('Test1',"gender",'Test1'[gender],"attribute","departure","value",'Test1'[departure_table]),

        SELECTCOLUMNS('Test1',"gender",'Test1'[gender],"attribute","total","value",'Test1'[total])

    )

   

1. Create a 100% stacked bar chart.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Anonymous]

@Anonymous Yuna, I think that's exactly what I need! Thank you! I'm not entirely sure how it will work with my table structure/data connection, but at least this gets me in the right direction. Much appreciated!

[amitchandak]

@Anonymous , In power bi you have two100% stacked bars (bar and column bar), Please try that.

https://www.tutorialgateway.org/create-100-stacked-bar-chart-in-power-bi/

[Anonymous]

What I need is one chart that will show the aggregate of the total along with the breakout of individual groups.

As another example, let's say I have sales per office. I would have bars for New York, Los Angeles, and Chicago. But then I also want a single bar at the top with all offices. Is there any way to get that total bar at the top?

Back to my original example of employee data, I want all of the employees on the top bar, then just the ones that departed on the bottom. The departures would be included in the top bar count (since they were employees on Jan 1). The point is to show ratio of the departures to the ratio of the total.