Skip to main content
cancel
Showing results for 
Search instead 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

Reply
bkr
Helper I
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 IDEmployeeManagers IDManager
1John Doe3Jane Austin
2Joe Smith6Jim Hans
3Jane Austin7Ricardo Best
4Anna Smith3Jane Austin
5Bruce Myers2Joe Smith
6Jim Hans7Ricardo Best

 

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

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
123abc
Resident Rockstar
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.

 
 
 

View solution in original post

2 REPLIES 2
123abc
Resident Rockstar
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.

 
 
 

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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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