Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
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.

 

EmpIDActive Jan 1Departure
1Activeno
2Activeno
3Activeyes
4nono
5Activeno
6Activeyes
7Activeyes
8Activeno
9nono

 

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.

v-yuaj-msft_0-1605164316180.png

 

  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]

)

v-yuaj-msft_1-1605164316184.png

 

  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])

    )

   v-yuaj-msft_0-1605164970642.png

 

 

  1. Create a 100% stacked bar chart.

Result:

v-yuaj-msft_1-1605164986610.png

 

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @Anonymous ,

 

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

v-yuaj-msft_0-1605164316180.png

 

  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]

)

v-yuaj-msft_1-1605164316184.png

 

  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])

    )

   v-yuaj-msft_0-1605164970642.png

 

 

  1. Create a 100% stacked bar chart.

Result:

v-yuaj-msft_1-1605164986610.png

 

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!

amitchandak
Super User
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/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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.

Attrition Mockup.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.