Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.