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>

 

[miguel.escobar]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.