Forum Discussion
How do I filter by rows and count the total?
I need to be able to filter on the columns below to create a new column that filters and counts based on them.
For example, if Type is "AWS" or "Azure" filter based on director and count the number of same title there are. Similary for Type that is not "AWS" or "Azure".
| Type | VP | Director | Title |
| Azure | Steve | Adam | Engineer |
| Azure | Steve | Adam | Data Science |
| AWS | Steve | Adam | Engineer |
| AWS | Steve | Rory | Engineer |
| On-Prem | Steve | Rory | Network |
| On-Prem | Steve | Rory | Network |
| VHOST | Steve | Rory | Network |
| Azure | Mary | Austin | Data Science |
| Azure | Mary | Austin | Data Science |
The new table with the column should look like this:
| VP | Director | Title | Count |
| Steve | Adam | Engineer | 2 |
| Steve | Adam | Data Science | 1 |
| Steve | Rory | Engineer | 1 |
| Steve | Rory | Network | 3 |
Mary | Austin | Data Science | 1 |
I tried to use the DAX GROUPBY and also the GROUPBY in power query. I'm not able to figure out a solution.
Please help me, if you can.
Thank you!
- Try this
Table = SUMMARIZE(Sheet1,Sheet1[VP],Sheet1[Director],Sheet1[Title],"Counts",COUNTROWS(Sheet1))
2 Replies
KRISHP1234Helper I
Try this
Table = SUMMARIZE(Sheet1,Sheet1[VP],Sheet1[Director],Sheet1[Title],"Counts",COUNTROWS(Sheet1))Hi ns1002 ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
