cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Total of population in 100% bar chart

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

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.

4 REPLIES 4
Anonymous
Not applicable

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

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
Not applicable

@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!

Super User

@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
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors