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>

 

[MD610]

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]

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

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

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]

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]

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]

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]

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