A problem of how many 'companies' have how many 'users'?

[Anonymous]

Hi, I have a list of user IDs and their companies and I need to know, how many companies have how many users. Expected result looks like this: (4 companies have each only 1 userID, 2 companies have 2 users in total and 2 companies have 3 users in total.)

and my data is a list of user IDs and Companies. It looks like this:

Company	UserID
A	1
A	2
A	3
B	4
B	5
C	6
D	7
E	8
F	9
G	10
G	11
G	12
H	13
H	14

I have a hard time even naming this problem and searching the internet for it. 🙂 Could you help in DAX or PowerQuery?

And a bonus question is creating data segments like: How many companies have between 1-2 users in total? How many have 3-5 users in total? Again plotting in in a chart like following:

[HotChilli · Accepted solution]

Powerbi won't let you put a measure on the axis so you could create a summary table e.g. 

TableE = SUMMARIZECOLUMNS( TableP[Company], "countOff", COUNTROWS(TableP)) 

which will allow you to put countOff on the axis and COUNT of Company in the Values.

---------------------

The 2nd part is a binning solution.  You can create a calculated column in the new summary table or use the binning features

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning