Counting issue in PowerPivot

[Alex5265]

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]

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]

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]

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]

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]

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	To
01 Zero to Five	0	5
02 Six to Ten	6	10
03 Eleven to Twenty	11	20
04 Twenty One to Fifty	21	50
05 Fifty One to One Hundred	51	100
06 One Hundred and One to Five Hundred	101	500

<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:

CalculatedColumn2	Number of employers
01 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 Total	4

<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]

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]

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]

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]

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.