Forum Discussion
Count by category
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.
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 , Create another table using
GroupedData =
SUMMARIZE(
YourTableName,
YourTableName[Location],
YourTableName[Response],
"Frequency", COUNT(YourTableName[Response])
)
3 Replies
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]))
)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
Carliniiiii , Create another table using
GroupedData =
SUMMARIZE(
YourTableName,
YourTableName[Location],
YourTableName[Response],
"Frequency", COUNT(YourTableName[Response])
)
