# Counting issue in PowerPivot



## Alex5265 (Jan 13, 2013)

Hi, </SPAN></SPAN></SPAN>

I'm trying to count the number of employers which have specific numbers of employees. </SPAN></SPAN></SPAN></SPAN>
For example, here is a simple table (the actual table is much larger):
</SPAN></SPAN></SPAN></SPAN>
Employer Country Product Number of employees</SPAN></SPAN></SPAN></SPAN>
A USA Bikes 10</SPAN></SPAN></SPAN></SPAN>
A USA Staplers 5</SPAN></SPAN></SPAN></SPAN>
A England Bikes 15</SPAN></SPAN></SPAN></SPAN>
B England Staplers 15</SPAN></SPAN></SPAN></SPAN>
B England Clocks 20</SPAN></SPAN></SPAN></SPAN>
B England Detergent 25</SPAN></SPAN></SPAN></SPAN>
C South Africa Tissues 15</SPAN></SPAN></SPAN></SPAN>
C South Africa Staplers 25</SPAN></SPAN></SPAN></SPAN>
C South Africa Clocks 10</SPAN></SPAN></SPAN></SPAN>
C Canada Tissues 10</SPAN></SPAN></SPAN></SPAN>
C USA Detergent 50</SPAN></SPAN></SPAN></SPAN>
D England Bikes 25</SPAN></SPAN></SPAN></SPAN>
D England Tissues 10</SPAN></SPAN></SPAN></SPAN>
D England Detergent 15</SPAN></SPAN></SPAN></SPAN>
D Zimbabwe Staplers 60</SPAN></SPAN>
</SPAN></SPAN>
If i put this data into a pivot table, I can create a measure, "Number of employers", using =countrows(DISTINCT(Table1[Employer])). The total is 4. </SPAN>
</SPAN></SPAN></SPAN>
I now want to get the number of employers per distinct number of employees. For example, employer A has a total of 30 employees, B has 60, C has 110, and D also has 110. I would like the pivot table to reflect that the number of employers with a total of 110 employees is 2, the number of employers with a total of 60 employees is 1, and the number of employers with a total of 30 employees is 1. But I am only able to show the number of employees per row in the original table, as below: </SPAN></SPAN></SPAN></SPAN>

Number of employees Number of employers</SPAN></SPAN></SPAN></SPAN>
5 1</SPAN></SPAN></SPAN></SPAN>
10 3</SPAN></SPAN></SPAN></SPAN>
15 4</SPAN></SPAN></SPAN></SPAN>
20 1</SPAN></SPAN></SPAN></SPAN>
25 3</SPAN></SPAN></SPAN></SPAN>
50 1</SPAN></SPAN></SPAN></SPAN>
60 1</SPAN></SPAN></SPAN></SPAN>
Grand Total 4</SPAN></SPAN></SPAN></SPAN>

In other words, is there a way to show the total number of employees per employer, and then aggregrate the employers rather than show them individually as A, B, C and D? </SPAN></SPAN></SPAN></SPAN>

I'm new to PowerPivot and DAX so I may be missing something fairly basic. </SPAN></SPAN></SPAN></SPAN>
Many thanks for any help. </SPAN></SPAN></SPAN></SPAN>


----------



## Laurent C (Jan 14, 2013)

You can put the number of employees on rows, but you can only put measures (in that case, "Number of employers") in the values area.


----------



## miguel.escobar (Jan 14, 2013)

Laurent C said:


> You can put the number of employees on rows, but you can only put measures (in that case, "Number of employers") in the values area.



as simple as it sounds, that's probably the easiest way to get the results that you need. In any other case, I'll recommend sharing your workbook so we can take a closer look at your data model and go from there.


----------



## Alex5265 (Jan 14, 2013)

Thanks for your replies. 

I'm not sure if I made myself clear (probably because I was/am confused). I have partially resolved the issue by using a SUMIF column in the original table to get the Total Employees per employer. This is fine for overall numbers, as it gives me the following: 


Total employees
Number of employers
30
1
60
1
110
2
Grand Total
4


<TBODY>

</TBODY>

That is an improvement on what I had before. But I also want to bring in other columns or slicers, and for the Total Employees figures to change when this happens. For example, a Female filter should show the total number of female employees, as well as the number of employers who have those number of female employees. A SUMIF column in the original table doesn't allow such filters to work, it just gives the total. 
What I'm hoping for is a measure (I guess) that will perform a kind of SUMIF function as well as taking into account any filters applied in the pivot. 

I can upload a file if necessary but I would appreciate any help using the initial hypothetical table.

thanks


----------



## Laurent C (Jan 15, 2013)

If my understanding is correct:
- there may be several rows in your table for each employer
- depending on current context (filter and current row/column item), you want to group employers by the number of employees.

This is called "banding".

Does this article help?
Alberto Ferrari : Banding with PowerPivot


----------



## Alex5265 (Jan 15, 2013)

Laurent C said:


> If my understanding is correct:
> - there may be several rows in your table for each employer
> - depending on current context (filter and current row/column item), you want to group employers by the number of employees.



Hi Laurent - what you describe sounds correct. 

Thanks also for bringing the banding article to my attention. I found it a bit hard to follow, and received errors until I added a RELATEDTABLE expression (thanks miguel.escobar). To clarify, I built Table2 below for the bands, and used the following as a calculated column in Table1: =CALCULATE(values(Table2[BandName]),filter(relatedtable(Table2),[Number of employees]>=Table2[From]&&[Number of employees]<=Table2[To]))

BandName      From       To01 Zero to Five0502 Six to Ten61003 Eleven to Twenty112004 Twenty One to Fifty215005 Fifty One to One Hundred5110006 One Hundred and One to Five Hundred101500

<TBODY>

</TBODY><COLGROUP><COL><COL span=2></COLGROUP>
However, I'm still not getting the results I need. If i use my original "Number of employers" measure against the new calculated column in a pivot, I get the following: 


CalculatedColumn2Number of employers01 Zero to Five</SPAN>1</SPAN>02 Six to Ten</SPAN>3</SPAN>03 Eleven to Twenty</SPAN>4</SPAN>04 Twenty One to Fifty</SPAN>3</SPAN>05 Fifty One to One Hundred</SPAN>1</SPAN>Grand Total4

<TBODY>

</TBODY><COLGROUP><COL><COL></COLGROUP>
The problem here is that employers are being counted more than once. Perhaps it is my measure that's the issue. I will keep fiddling around.


----------



## Laurent C (Jan 16, 2013)

Because I do not feel comfortable with Table1 and Table2 names, for the following I changed the names to the following:

EmployerSizeGroups { ( BandName, From, To )}
Employers { (EmployerName, ... )}
Data { ( Employer, Employee, Product, ... ) } linked to Employers only.

I assume you have an Employers lookup table.

You want to calculate [Some Measure] for the Employers whose number of employees (for the current selection) can be associated with the appropriate EmployerSizeGroup. This can be done with the following pattern:

```
=CALCULATE(
 [Some measure],
 GENERATE(
  Employers,
  FILTER(
   EmployerSizeGroups,
   [Number of employees] >= EmployerSizeGroups[From]
   && [Number of employees] < EmployerSizeGroups[From]
  )
 ) 
 )
```

The GENERATE expression will iterate through each Employer within the current context. For each Employer it will apply the FILTER expression on the EmployerSizeGroups table. If the result of the FILTER expression is not BLANK it will return return the Employer and the corresponding EmployerSizeGroup. 

EDIT: Of course, this must be entered as a calculated measure.


----------



## Alex5265 (Jan 16, 2013)

Thanks Laurent for your persistence. Unfortunately I can't seem to make this work. For [some measure] I inserted my original "Number of employers" =countrows(DISTINCT(Table1[Employer])) , renamed =countrows(distinct(Data[Employer])). I also changed the second EmployerSizeGroups[From] in your pattern to EmployerSizeGroups[To], assuming this was a typo. Thus:
 =CALCULATE([Number of employers],GENERATE(Employers,filter(EmployerSizeGroups,[Number of employees]>=EmployerSizeGroups[From]&&[Number of employees]<=EmployerSizeGroups[To])))

It didn't work as a measure ([Number of employees] couldn't be found or was not allowed to be used). As a calculated column, it returned a "1" for each row - at least this is something! However, I suspect it needs to work as a measure in order to do the job. I don't know enough about GENERATE (or CALCULATE) to tell whether we are on the right track or not. 

If anyone has any more ideas, obviously I would be grateful, otherwise I'll wait until some of my colleagues start using PowerPivot and pick their brains.


----------



## miguel.escobar (Jan 16, 2013)

Alex, 

Can you upload a sampe workbook with dummy data? Also, if you want...what I usually try to do is that I ask people to write their formulas in regular excel formulas and then I translate those into dax sortofspreak.

With a copy of your workbook we can take a real look at how your data is being managed (perhaps there's a relationship issue).

best!
Miguel


----------



## Alex5265 (Jan 16, 2013)

Thanks Miguel, 

First, let me say that all the data is in a single table (about 10,000 employers in the original), so relationships are not really an issue. If a solution can be applied to the simple dummy table I first posted, then it should translate to the real data. 

Let me try a different approach to the question. The table can be pivoted as follows: 


Sum of Number of employees
Country
Employer
Canada
England
South Africa
USA
Zimbabwe
Grand Total
A</SPAN>
15</SPAN>
15</SPAN>
30</SPAN>
B</SPAN>
60</SPAN>
60</SPAN>
C</SPAN>
10</SPAN>
50</SPAN>
50</SPAN>
110</SPAN>
D</SPAN>
50</SPAN>
60</SPAN>
110</SPAN>
Grand Total
10
125
50
65
60
310


<TBODY>

</TBODY>
The question I need to answer is how many employers are there with, for example, 15 employees in the USA? The table above shows there is only 1 (A). Or, how many employers have 50 or more employees in England? 2 (B and D). It's easy enough to sum these manually, but I'd like a measure to calculate them automatically, whenever the pivot changes. That is, to sum the distinct number of employers which have 10 employees, 15 employees, 50 employees, etc. in each country. 

I hope this makes a bit more sense.


----------



## Alex5265 (Jan 13, 2013)

Hi, </SPAN></SPAN></SPAN>

I'm trying to count the number of employers which have specific numbers of employees. </SPAN></SPAN></SPAN></SPAN>
For example, here is a simple table (the actual table is much larger):
</SPAN></SPAN></SPAN></SPAN>
Employer Country Product Number of employees</SPAN></SPAN></SPAN></SPAN>
A USA Bikes 10</SPAN></SPAN></SPAN></SPAN>
A USA Staplers 5</SPAN></SPAN></SPAN></SPAN>
A England Bikes 15</SPAN></SPAN></SPAN></SPAN>
B England Staplers 15</SPAN></SPAN></SPAN></SPAN>
B England Clocks 20</SPAN></SPAN></SPAN></SPAN>
B England Detergent 25</SPAN></SPAN></SPAN></SPAN>
C South Africa Tissues 15</SPAN></SPAN></SPAN></SPAN>
C South Africa Staplers 25</SPAN></SPAN></SPAN></SPAN>
C South Africa Clocks 10</SPAN></SPAN></SPAN></SPAN>
C Canada Tissues 10</SPAN></SPAN></SPAN></SPAN>
C USA Detergent 50</SPAN></SPAN></SPAN></SPAN>
D England Bikes 25</SPAN></SPAN></SPAN></SPAN>
D England Tissues 10</SPAN></SPAN></SPAN></SPAN>
D England Detergent 15</SPAN></SPAN></SPAN></SPAN>
D Zimbabwe Staplers 60</SPAN></SPAN>
</SPAN></SPAN>
If i put this data into a pivot table, I can create a measure, "Number of employers", using =countrows(DISTINCT(Table1[Employer])). The total is 4. </SPAN>
</SPAN></SPAN></SPAN>
I now want to get the number of employers per distinct number of employees. For example, employer A has a total of 30 employees, B has 60, C has 110, and D also has 110. I would like the pivot table to reflect that the number of employers with a total of 110 employees is 2, the number of employers with a total of 60 employees is 1, and the number of employers with a total of 30 employees is 1. But I am only able to show the number of employees per row in the original table, as below: </SPAN></SPAN></SPAN></SPAN>

Number of employees Number of employers</SPAN></SPAN></SPAN></SPAN>
5 1</SPAN></SPAN></SPAN></SPAN>
10 3</SPAN></SPAN></SPAN></SPAN>
15 4</SPAN></SPAN></SPAN></SPAN>
20 1</SPAN></SPAN></SPAN></SPAN>
25 3</SPAN></SPAN></SPAN></SPAN>
50 1</SPAN></SPAN></SPAN></SPAN>
60 1</SPAN></SPAN></SPAN></SPAN>
Grand Total 4</SPAN></SPAN></SPAN></SPAN>

In other words, is there a way to show the total number of employees per employer, and then aggregrate the employers rather than show them individually as A, B, C and D? </SPAN></SPAN></SPAN></SPAN>

I'm new to PowerPivot and DAX so I may be missing something fairly basic. </SPAN></SPAN></SPAN></SPAN>
Many thanks for any help. </SPAN></SPAN></SPAN></SPAN>


----------



## miguel.escobar (Jan 16, 2013)

so basically you just need a measure that will tell you the amount of employers that you have per country?
Or do you need the banding/range also? like, I want to know how many employers have 15 or less employees,  or between 16 and 30, etc


----------



## Alex5265 (Jan 16, 2013)

I can get the number of employers per country using a countrows(distinct()) expression. But the number of employees is the crucial part. Ideally the number of employees would be banded, but even simply showing unbanded numbers would make me happy (e.g. how many employers have a TOTAL of 15 employees in the USA, how many have a total of 50 employees in the USA?).


----------



## miguel.escobar (Jan 16, 2013)

Alex5265 said:


> I can get the number of employers per country using a countrows(distinct()) expression. But the number of employees is the crucial part. Ideally the number of employees would be banded, but even simply showing unbanded numbers would make me happy (e.g. how many employers have a TOTAL of 15 employees in the USA, how many have a total of 50 employees in the USA?).



Give me like 15 min and I'll give you a file with the answer


----------



## miguel.escobar (Jan 16, 2013)

Sorry for getting pass the 15 minutes, just got an email and needed to answer it and also was trying to find the article that would help you.

Here's the workbook:
https://dl.dropbox.com/u/54063091/Solution.xlsx

and it's based on this (more or less):
SQLBI - Marco Russo : ABC Analysis in PowerPivot

basically you could do 2 approaches:
Approach 1 (the easy one): get a calculated column that will sum the total amount of employees in your DIM table (table 3 in the workbook)
Approach 2 (the denormalized table): basically getting that same result from above but in your table 1 using just the info from that table

Let me know if this is what you were expecting.

Best!
Miguel


----------



## Alex5265 (Jan 16, 2013)

Thanks Miguel for the two approaches as well as the article - it will take me a while to get my head around this. 

Meanwhile, the results appear to match what I posted on Jan 15th, 2013, 10:28 AM. I may have misled you by emphasising "TOTAL" employees. Yes, the calculated column works fine for the total amount of employees. But if I want to filter by Country, say, I would expect there to be one employer with 15 employees in the USA and one employer with 50 employers in the USA. Instead, the result is one employer with a total of 30 employees and one employer with a total of 110 employees. In other words, the total number of employees is always the total, regardless of current context.


----------



## miguel.escobar (Jan 16, 2013)

Easiest way would be to have 2 calc columns, one for the Employer & Country combination and the other one provided on the workbook for just the Employer.
I'll try and see if there's a better way of doing this but probably Laurent will get ahead of me


----------



## Alex5265 (Jan 16, 2013)

Miguel/Laurent, 
You have the patience of saints. I also thought about having more than one calc column - for the small table this would be fine, but the real-world table has maybe 6 or 8 columns and I would want all the combinations to be available (e.g. employer/country/product, employer/country/year, employer/year, employer/country/product/year, etc.). 
regards
Alex


----------



## miguel.escobar (Jan 16, 2013)

Alex5265 said:


> Miguel/Laurent,
> You have the patience of saints. I also thought about having more than one calc column - for the small table this would be fine, but the real-world table has maybe 6 or 8 columns and I would want all the combinations to be available (e.g. employer/country/product, employer/country/year, employer/year, employer/country/product/year, etc.).
> regards
> Alex



Best way would be to use cubeformulas and parameters. The thing about using calc columns is that they don't have "dynamic" values, in other words, when you exit out the powerpivot window...what you saw on that window, that's basically what you have to play with.
There are other ways of creating a complete array of columns within one measure (meaning multiple columns within 1 measure) but those type of formula SERIOUSLY take a big time to process/calculate.

I'm going to wait on what Laurent has to say cause it might be clever than using the cubeformulas.

Best!
Miguel


----------



## Laurent C (Jan 17, 2013)

Using [From] twice was indeed a typo. The expression must be used in a measure.

I assumed [Number of employees] was a measure. If this is a column in your data table, then you would have to use SUM(Data[Number of employees]) instead.


----------



## Laurent C (Jan 17, 2013)

The final formula formula would look like this:

```
=CALCULATE(
 [Some measure],
 GENERATE(
  Employers,
  FILTER(
   EmployerSizeGroups,
   SUM(Data[Number of employees]) >= EmployerSizeGroups[From]
   && SUM(Data[Number of employees]) < EmployerSizeGroups[To]
  )
 ) 
)
```
Remember the [Some measure] is just a placeholder for any measure you would like to calculate. The important part is the filtering of these employers that belong to the selected bands for the current selection.


----------



## Alex5265 (Jan 13, 2013)

Hi, </SPAN></SPAN></SPAN>

I'm trying to count the number of employers which have specific numbers of employees. </SPAN></SPAN></SPAN></SPAN>
For example, here is a simple table (the actual table is much larger):
</SPAN></SPAN></SPAN></SPAN>
Employer Country Product Number of employees</SPAN></SPAN></SPAN></SPAN>
A USA Bikes 10</SPAN></SPAN></SPAN></SPAN>
A USA Staplers 5</SPAN></SPAN></SPAN></SPAN>
A England Bikes 15</SPAN></SPAN></SPAN></SPAN>
B England Staplers 15</SPAN></SPAN></SPAN></SPAN>
B England Clocks 20</SPAN></SPAN></SPAN></SPAN>
B England Detergent 25</SPAN></SPAN></SPAN></SPAN>
C South Africa Tissues 15</SPAN></SPAN></SPAN></SPAN>
C South Africa Staplers 25</SPAN></SPAN></SPAN></SPAN>
C South Africa Clocks 10</SPAN></SPAN></SPAN></SPAN>
C Canada Tissues 10</SPAN></SPAN></SPAN></SPAN>
C USA Detergent 50</SPAN></SPAN></SPAN></SPAN>
D England Bikes 25</SPAN></SPAN></SPAN></SPAN>
D England Tissues 10</SPAN></SPAN></SPAN></SPAN>
D England Detergent 15</SPAN></SPAN></SPAN></SPAN>
D Zimbabwe Staplers 60</SPAN></SPAN>
</SPAN></SPAN>
If i put this data into a pivot table, I can create a measure, "Number of employers", using =countrows(DISTINCT(Table1[Employer])). The total is 4. </SPAN>
</SPAN></SPAN></SPAN>
I now want to get the number of employers per distinct number of employees. For example, employer A has a total of 30 employees, B has 60, C has 110, and D also has 110. I would like the pivot table to reflect that the number of employers with a total of 110 employees is 2, the number of employers with a total of 60 employees is 1, and the number of employers with a total of 30 employees is 1. But I am only able to show the number of employees per row in the original table, as below: </SPAN></SPAN></SPAN></SPAN>

Number of employees Number of employers</SPAN></SPAN></SPAN></SPAN>
5 1</SPAN></SPAN></SPAN></SPAN>
10 3</SPAN></SPAN></SPAN></SPAN>
15 4</SPAN></SPAN></SPAN></SPAN>
20 1</SPAN></SPAN></SPAN></SPAN>
25 3</SPAN></SPAN></SPAN></SPAN>
50 1</SPAN></SPAN></SPAN></SPAN>
60 1</SPAN></SPAN></SPAN></SPAN>
Grand Total 4</SPAN></SPAN></SPAN></SPAN>

In other words, is there a way to show the total number of employees per employer, and then aggregrate the employers rather than show them individually as A, B, C and D? </SPAN></SPAN></SPAN></SPAN>

I'm new to PowerPivot and DAX so I may be missing something fairly basic. </SPAN></SPAN></SPAN></SPAN>
Many thanks for any help. </SPAN></SPAN></SPAN></SPAN>


----------



## MD610 (Jan 17, 2013)

Row Labels     Measure 1
30                        1
60                        1
110                      2
Grand Total           4

Is this the result you want?

If so it only takes one calculated column in addition to the first measure you created:
=calculate(SUM([Employees]),FILTER('Table1','Table1'[Employer]=EARLIER('Table1'[Employer])))

This calculated column will give you the total employees for each employer.

Drop this new Column into your Row Labels.  Add your first measure to Values:
[Measure 1]:=countrows(DISTINCT(Table1[Employer]))


----------



## miguel.escobar (Jan 17, 2013)

miguel.escobar said:


> Sorry for getting pass the 15 minutes, just got an email and needed to answer it and also was trying to find the article that would help you.
> 
> Here's the workbook:
> https://dl.dropbox.com/u/54063091/Solution.xlsx
> ...





MD610 said:


> Row Labels     Measure 1
> 30                        1
> 60                        1
> 110                      2
> ...



Basically the same approach on both posts, but the thing is MD, that he wants the Row series to be *dynamic*, in other words, when he clicks a country within a slicer, those values should change to 15 and other lower values.. THAT's the tricky part


----------



## MD610 (Jan 17, 2013)

I see.  I think I have a solution that works in that scenerio too using a disconnected table:

First, I created a single column table listing values from 5 - 200 in increments of 5.  You could make it bigger or smaller as needed.  I used EmpCounts for the table name and CntID for the column header. Add this as a linked to PowerPivot.  Do not relate it to anything.

Now you need to create 2 measures.

Measure 1:
[EmployeeTotals]:=CALCULATE(SUM('Table1'[Employees]), FILTER( ALLSELECTED('Table1'), SUMX( FILTER('Table1', EARLIER('Table1'[Employer])='Table1'[Employer]), 'Table1'[Employees]))

This is the measure that is basically doing the "dynamic" banding.

Measure 2:
[EmployersWithEmpCnt]:=CALCULATE(DISTINCTCOUNT('Table1'[Employer]), FILTER('Table1', IF(HASONEVALUE('EmpCounts'[CntID]),VALUES('EmpCounts'[CntID])=[EmployeeTotals],blank())))

This measure checks to see if the the [EmployeeTotal] matches a row in the 'EmpCounts' and counts the Employer if it does.

When you create your pivot, use the 'EmpCounts'[CntID] field in rows and [EmployersWithEmpCnt] in values.

If you add Country as a slicer, you will see the values update accordingly in the Pivot.

2 things to note:
1. There will be no Grand Totals because the second measure requires a HASONEVALUE() to prevent errors.  However, I would think the Grand Totals aren't the main thing you need from this pivot.
2. The Country Slicer will appear grayed out by default since it is unrelated to the 'EmpCounts' table. It will still work though. If you right-click on the slicer and go to slicer settings, you can turn off the check for Visually indicate items with no data.  This should fix the slicer appearance, although it won't update with other slicers/filters anymore.


----------



## Alex5265 (Jan 17, 2013)

MD, 

Bravo, your solution worked! 

Laurent, I haven't yet been able to make yours work, but that probably says more about my lack of skills. I will endeavour to understand your method as well, since it doesn't hurt to have more than one approach. 

Thanks to you both and Miguel for helping to resolve this. 

regards
Alex


----------



## Laurent C (Jan 18, 2013)

With a measure called [Sum of Number of Employees] := SUM(Data[Number of employees]), the pattern would look like this. This was my first post (except the measure had the same name as your actual column).


```
=CALCULATE(
  [Some measure];
 GENERATE(
  Employers;
  FILTER(
   EmployerSizeGroups;
   [Sum of Number of Employees] >= EmployerSizeGroups[From]
   && [Sum of Number of Employees] <= EmployerSizeGroups[To]
  )
 ) 
)
```

When I proposed to replace [Sum of Number of Employees] with the expression SUM(Data[Number of employees]), I was a little quick and forgot something important: an expression (in a FILTER or any iterator function) is not automatically calculated for the current row context. A CALCULATE expression is required.

So, the formula without any intermediary measure would be (although I prefer the first one):

```
=CALCULATE(
  [Some measure];
 GENERATE(
  Employers;
  FILTER(
   EmployerSizeGroups;
   CALCULATE(SUM(Data[Number of employees])) >= EmployerSizeGroups[From]
   && CALCULATE(SUM(Data[Number of employees])) <= EmployerSizeGroups[To]
  )
 ) 
)
```


----------



## Alex5265 (Jan 20, 2013)

Laurent,
Thank you for your persistence! It seems this is an equally good solution - the benefit is that the banding table contains ranges, so it can be easily applied to my real data (with up to 800 employees per employer). I think MD's solution works well with the dummy data because all the employee numbers were multiples of 5. 
Thanks again
Alex


----------

