Visualize span of control

[bkr]

Hi all,

I have a table with employees, their ID, their line managers and their line managers' id for an organization of ~2,000 people.

Employee ID	Employee	Managers ID	Manager
1	John Doe	3	Jane Austin
2	Joe Smith	6	Jim Hans
3	Jane Austin	7	Ricardo Best
4	Anna Smith	3	Jane Austin
5	Bruce Myers	2	Joe Smith
6	Jim Hans	7	Ricardo Best

I'd like to prepare a chart that shows how many managers have one subordinate, how many have two Subordinates etc:

Here 12 Managers have one subordinate, 15 have two subordinates, 27 managers have 3 subordinates etc.

Can you help me achieving this? I tried to calculate a span of control measure but I cannot use it as x axis in a bar chart. Any simple ways to do this? Thanks!

[123abc · Accepted solution]

To visualize the span of control in Power BI, you can follow these steps:

1. Data Preparation:

   Make sure you have your data loaded into Power BI. It should look similar to the table you provided, with columns for Employee ID, Employee Name, Manager ID, and Manager Name.

2. Create a New Calculated Column:

   In Power BI, you can create a new calculated column to count the number of subordinates each manager has. You can use the DAX (Data Analysis Expressions) formula to achieve this. Here's the formula:

Subordinate Count =
CALCULATE(
COUNTROWS('YourTableName'),
FILTER('YourTableName', 'YourTableName'[Manager ID] = EARLIER('YourTableName'[Employee ID]))
)

1. Replace 'YourTableName' with the actual name of your table.

2. Create a Bar Chart:

   Now, you can create a bar chart using the 'Subordinate Count' as the x-axis. Follow these steps:

   • Click on "Visualizations" on the left pane.
   • Select "Bar Chart."
   • Drag the "Subordinate Count" field to the "Values" section of the visual.
   • You can also drag the "Subordinate Count" field to the "Axis" section for more granularity.

3. Customize the Bar Chart:

   You can further customize the bar chart as per your requirements. For example, you can add labels, titles, and adjust the formatting to make it visually appealing.

4. Analyze the Data:

   After creating the bar chart, you will see the distribution of managers based on the number of subordinates they have.

Here's how you can use Power BI to visualize the span of control in your organization. This will show you how many managers have one subordinate, two subordinates, and so on, which is what you wanted to achieve.

Remember to adjust the table and column names in the DAX formula to match your actual data structure in Power BI.

[bkr]

Thank you, this works! I hadn't come across EARLIER earlier 😉