March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to Solution.
Hi @Anonymous ,
Based on your description, I did a test, the table for testing is as follows.
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]
)
Test2 =
UNION(
SELECTCOLUMNS('Test1',"gender",'Test1'[gender],"attribute","departure","value",'Test1'[departure_table]),
SELECTCOLUMNS('Test1',"gender",'Test1'[gender],"attribute","total","value",'Test1'[total])
)
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.
Hi @Anonymous ,
Based on your description, I did a test, the table for testing is as follows.
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]
)
Test2 =
UNION(
SELECTCOLUMNS('Test1',"gender",'Test1'[gender],"attribute","departure","value",'Test1'[departure_table]),
SELECTCOLUMNS('Test1',"gender",'Test1'[gender],"attribute","total","value",'Test1'[total])
)
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 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!
@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/
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |