Creating table for finding number of people under certain catogery

[unnijoy]

I have a table with country name and employee ID.

When ever an employee is awarded the country name and employee id will be added to the table. I need to create a table which will show like below. How many people from Ind got awarded only 1 time, 2 time,3 time and more than 3 time. The logic we use is how many times the employee id is repeating. I haved attached sample data that i use to create the below table. Please help me how we can create it in powerbi

Country	One time	Two times	Three times	More than three times
Ind	0	1	2	1
MAL	0	0	1	1
CHI	1	0	1	0

Country	Empid
Ind	123
Ind	123
Ind	123
Ind	123
Ind	234
Ind	234
Ind	234
ind	12
ind	12
ind	12
MAL	345
MAL	345
MAL	345
MAL	546
MAL	546
MAL	546
MAL	546
CHI	12
CHI	23
CHI	23
CHI	23

[Greg_Deckler]

I am not 100% that I am clear on this but I believe you want a diconnected table and a measure like below. See the attached PBIX file.

Measure = 
    VAR __Count =
        SWITCH(MAX('Categories'[Category]),
            "One time",COUNTROWS(FILTER(SUMMARIZE('Table',[Empid],"__Count",COUNTROWS('Table')),[__Count]=1)),
            "Two times",COUNTROWS(FILTER(SUMMARIZE('Table',[Empid],"__Count",COUNTROWS('Table')),[__Count]=2)),        
            "Three times",COUNTROWS(FILTER(SUMMARIZE('Table',[Empid],"__Count",COUNTROWS('Table')),[__Count]=3)),
            COUNTROWS(FILTER(SUMMARIZE('Table',[Empid],"__Count",COUNTROWS('Table')),[__Count]>3))
        )
RETURN
    IF(ISBLANK(__Count),0,__Count)

[unnijoy]

Hi @Greg_Deckler 

Thanks for your help. Its working. Can you pleae help me to find the number if i am adding a catogery column in the same data. Than means we have to find how many people are awarded for 1 single catogery, 2 catogerys 3 catogerys. I am attaching the data and the expected table below.

Country	Awarded - one Category	Awarded - 2 Categories	Awarded - 3 Categories	Awarded - 4 Categories	Awarded - > 4 Categories
Ind	2	0	1	0	0
MAL		1		0	1
CH	1	1	0	0	0

Country	Empid	Cat
Ind	123	a
Ind	123	a
Ind	123	b
Ind	123	c
Ind	234	a
Ind	234	a
Ind	234	a
ind	12	b
ind	12	b
ind	12	b
MAL	345	b
MAL	345	a
MAL	345	a
MAL	546	b
MAL	546	a
MAL	546	c
MAL	546	d
CHI	12	a
CHI	23	b
CHI	23	b
CHI	23	c

[unnijoy]

Hi Anyone can help with the above request please...

[unnijoy]

@amitchandak  can you please help me with the above.