Count by category

[Carliniiiii]

Hello Power BI community, 

I have following data set: 

Project	Location
Project1	A
Project1	A
Project1	B
Project2	B
Project2	C

I would like to count each different location once per project. So I want to create a new table with following informations for further analysis: 

Location	Frequency
A	1
B	2
C	1

Many thanks for your help or ideas. 

[bhanu_gautam · Accepted solution]

@Carliniiiii , You can use Summarize function for this and create a new table by going into modelling section

NewTable =
SUMMARIZE(
'YourTableName',
'YourTableName'[Location],
"Frequency", COUNTROWS(SUMMARIZE('YourTableName', 'YourTableName'[Project], 'YourTableName'[Location]))
)

[Carliniiiii]

@bhanu_gautam , I have another question in a similar format. 

I have following table: 

Location	Quarter	Response
A	Q1	Yes
A	Q2	No
A	Q3	No
A	Q4	Yes
B	Q1	No
B	Q2	Yes
B	Q3	Yes
B	Q4	Yes

And I would like it to be grouped by both location as well as yes/no answers. I have limited the example to one year only but it should work over multiple years.

Locations	Response	Frequency
A	Yes	2
A	No	2
B	Yes	3
B	No	1

Many thanks 

[bhanu_gautam · Accepted solution]

@Carliniiiii , Create another table using

GroupedData =
SUMMARIZE(
YourTableName,
YourTableName[Location],
YourTableName[Response],
"Frequency", COUNT(YourTableName[Response])
)

[bhanu_gautam · Accepted solution]

@Carliniiiii , You can use Summarize function for this and create a new table by going into modelling section

NewTable =
SUMMARIZE(
'YourTableName',
'YourTableName'[Location],
"Frequency", COUNTROWS(SUMMARIZE('YourTableName', 'YourTableName'[Project], 'YourTableName'[Location]))
)