cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Visualize span of control

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!

1 ACCEPTED SOLUTION
Resident Rockstar

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.

2 REPLIES 2
Resident Rockstar

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.

Helper I

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors