Best Practice for Counting Employees

[Anonymous]

So I have a dataset of companies and their employee ID's. I'm trying to figure out the best way to count the number of companies by their employee size, based on the number of ID's they have. I currently have this:

Which was working at first but it it's been giving me problems when I import additonal data for whatever reason, so I want to find another way to do it. My base table I'm basing it on is this:

My goal is to put together a chart showing different size ranges but only inlcuding "A" status employees. I've tried putting this together on a chart as well and for some reason also, the legend displays the ranges out of order. I've been trying to get them in numerical order:

None of these counts are correct by the way. They were correct before I went back to the query editor and imported the "T" status employees. I'm not sure why adding them threw my switch statement off, but I just want to find a clean way to gather and display this data correctly. What is this the best practice(s) for achieving the above?? Thanks!

[Anonymous · Accepted solution]

Couple things come to mind:

1) Your SWITCH( TRUE() ) function works well, but it's a little cumbersome.

Try this:

[Employee Size Range] =
VAR EmpNum = [Employee Count]
RETURN
    SWITCH (
        TRUE (),
        EmpNum <= 25, "0-25",
        EmpNum <= 50, "26-50",
        EmpNum <= 100, "51-100",
        EmpNum <= 200, "101-200",
        "200+"
    )

By creating a variable EmpNum, you don't have to evaluate the [Employee Count] every time.  This will run faster.

Also, SWTICH() tries each logical comparison in order, so you don't need to use AND.  If it fails the first logical comparison (saying it's less than 25), then by default it will be greater than 25.  No need to check that part again.

Power BI always sorts columns in alphabetical order.  If you ever want to sort columns in a different order, you need to have a column in your data model to sort by.

Good news is, you can use the same template from your measure to create your sorting column:

EmployeeSizeRangeSort =
VAR EmpNum = [Employee Count]
RETURN
    SWITCH (
        TRUE (),
        EmpNum <= 25, 1,
        EmpNum <= 50, 2,
        EmpNum <= 100, 3,
        EmpNum <= 200, 4,
        5
    )

Now, click on the [Employee Size Range] column.  Click the MODELING TAB.  Then Click SORT BY COLUMN.  Click [EmployeeSizeRangeSort]

Hope this helps,

~ Chris H

[Anonymous]

Also to futher clarify, Here's what I mean by different services, service levels, company sets and empstatuses:

Sometimes with having these and trying to achieve distinct counts, there is some form of miscounting or double/triple counting something because of the element of various services etc. a company can have,

[Anonymous]

Thanks Chris! This worked flawlessly. I have a few other questions about this scenario. So I have my numbers the way I want them on my graph now, with the legend in order:
 And here is my formula for 'Employee Count' which I previously did not show that is the VAR for Employee Size Range:

My issue is now when I try to import employees of the status type of 'T', all the numbers on my graph change:

Even if I set a filter on the graph itself to only show 'A' status counts, the numbers do not revert to the numbers in the 1st graph above. I feel it has to do with my 'Employee Counts' formula. I've tried swapping out the 'servicelevel' field I'm using in my ALLEXCEPT clause but nothing seems to work. I'm not sure how to do this piece because with each company they can have multiple services, service levels, company sets and empstatuses. My intent is that regardless of the number of these things they have, I am still able to do a distinct count of the number of companies and their active employee counts (I'm pulling in termed employees for another purpose):

Any ideas?

Thanks!