count of employees per compagny to filter on report

[ornicus]

Hello,

I have been unsuccesfully ttrying this morning to create a count of employees per compagny in my data in order to later filter out compagnies with 100 employees or less.

I need to do this else Power BI can not handle the number of companies and associated data and produce errated graphs.

So I have an excel input with columns Name, Surname, Company and I tried to create a calculated column or a linked table (not a mesure because I can not filter a report on a mesure).

In excel I would do a pivot table quickly but here I dont succeed to define a correct formula in DAX.

I need some guidance on what would be the best way (calculated column, or table or other solution) to be able to later in my report, filter the companies depending their number of employees.

thanks for reading

[prateekraina]

Hello @ornicus,

It is very simple. You just need to add Company Name and Employee Name in Table/Matrix visual and then simply click on Employee Name in values section and change it to Count or Count(DISTINCT) as per your requirement.
Refer below screenshots:
Dummy DatasetResult graph as per the problem setChoose count option

Let me know if anything else is required.

Prateek Raina

[Jit007]

this is not working in my case

my data

Emp  Score

11     95

11    75

12   95

12   65

i want to show count of empl. per ave category

[ornicus]

Thanks for your answer, It kind of works. (I had already built a matrix like this but without seeing I could then filter on the "number of Company"...I guess after trying so many things, I got confused.

So now I can filter my report.

The only downside is that I have a table that the users dont really need to see and that I would need to "hide".

Would it be possible to have this "number o Company" in a column in my data. Hence I could add this column to the report filter directly?

[prateekraina · Accepted solution]

Hi @ornicus,

Ignore my previous post. You can have the total in a column also.

User below Dax as per your dataset:

CountOfEmployeesColumn = CALCULATE(COUNT(CompanyDetails[Name]),ALLSELECTED(CompanyDetails[Name]),ALLSELECTED(CompanyDetails[Surname]))

 You will get dataset like below:

You can then use this columm to filter report.

Thanks 

Prateek Raina

[ornicus]

Wow, that is great!

It works if I build an example like you did but on my other table not??

Is it important the calculated column is right after the company column? Any idea what could be blocking?

edit: I made a copy of name, surname and company into another table. Then I added your DAX code and it works. But if I then add another column (examply "city"), then the results are wrong again...

I don´t get it.

[prateekraina]

Hi @ornicus,

Apart from company, you need to keep all other other columns in ALLSELECTED. Then only you will get count of company across all those fields. 

Prateek Raina

[ornicus]

Understood, thanks for the clarification.

Since I have a lot of columns and mayby create more in the future, I think I will create a calculated table with only name, surname and company (linked to the main table), where I will use your code to get the number of empoyees. Hence it will not be affected by any further change in the main table.

[prateekraina]

Hi @ornicus,

In that case, just keep Company and Name only. No use of Surname also.
Also, please like the post if you think i have solved your problem 🙂

Prateek Raina

[ornicus]

More problems hahaha (nervous laugh)!

So I created a second table with SUMMARIZE, then I add the code to get the number of employees. It is all good.

But in order to use the CountOfEmployees column in my report as a filter of the main data, I create a 1:1 relathionship (on the ID column) and magically, all the calculated values are now equal to 1! (I added the ID coulmn in the ALLSELECTED).

I dont understand why the result of CountOfEmployees could change because of the relationship. Any clues?

[prateekraina]

Hi @ornicus,

See you need to understand this, you are trying to find out Count of Employees per Company so you cannot store this information as a column in your dataset since you have Name column present so for each row the count will be 1.

You can however create a measure using below DAX and then add that in your table/matrix visual and then you can filter on that measure.

CountoFEmployees = COUNT(CompanyDetails[Name])

Hope this suits your needs.

Prateek Raina

[prateekraina]

You can also filter then using the same "Count Of names" column to filter employees greater than 100.