Forum Discussion
(First Dashboard) Calculating Percentages Against Multiple Values
Good morning, everyone.
I have created a data model and connected relationships. I have a few, very basic visuals as I try and learn Power BI, but I've hit a snag. I have multiple criteria - As and example:
| Total Staff | Resignations | |
| Cleaning | 20 | 1 |
| Admin | 5 | 0 |
| Managers | 6 | 0 |
| Auditors | 15 | 2 |
| Payroll | 3 | 0 |
| HR | 2 | 0 |
| Helpdesk | 5 | 2 |
| Security | 12 | 3 |
| TOTAL | 68 | 8 |
What I'd like to do is calculate percentages agaisnt this. I don't currently have measures that calculate the invidiaul numbers of staff per service line, this is done entierly by the visual tools, so on instinct I want to calculate 8 different measures, then divide them by another 8 measures... Which, while it would work, feels like I'm missing the point.
Is there a simple way to ask the visual tools to give me a percentage of something calculated against a value that exists only in the visual tool itself?
Sorry if this is unclear.
The easiest visualizations for showing percentages are the 100% Stacked Bar, 100% Stacked Column, Pie, and Donut charts. Given all the partitions of a total, these can provide you with percentages. You will, however, need a third column that has Remaining Staff (Total Staff minus Resignations). Then, using a 100% bar chart with Axis of Department (Cleaning Staff, Admin, Managers, ...), you add the Resignations and the Remaining Staff into the Value field.
4 Replies
Anonymous can you share what is the formula for your % calculations?
I sure can!
I've isolated the data in the queries, added a simple DistinctCount (Employee Number) to give me the total staff, then let the visuals do the rest of the work by letting it break down the staff by their relevant service line.
For the terminatons, I've let the visuals do ALL the work. My data includes the employee number, the service line, so I've just let the visual break them down for me.
I'm thinking, if I add a "Total Terminations" measure that is just a DistinctCount of all staff leaving, then do a simple X/Y, the visuals should resolve the rest into their subcatagories, right?
I'm sorry, I know this isn't totally clear.
The easiest visualizations for showing percentages are the 100% Stacked Bar, 100% Stacked Column, Pie, and Donut charts. Given all the partitions of a total, these can provide you with percentages. You will, however, need a third column that has Remaining Staff (Total Staff minus Resignations). Then, using a 100% bar chart with Axis of Department (Cleaning Staff, Admin, Managers, ...), you add the Resignations and the Remaining Staff into the Value field.
