Forum Discussion
Pivot Table Count Unique or Similar Values
Hi, I was wondering if there’s a way to create a pivot table that counts unique values. I have tried adding to data model and selecting distinct count. This works, but then I run into a problem where ...
Well my issue is this:
- trying to tally students for a class.
- while it shows the subtotal, it also shows the count 1 for each student row:
| Class 1 | Name1 | 1 |
| Name2 | 1 | |
| Name2 | 1 | |
| Class 1 total | 3 | |
| Class 2 | Namex | 1 |
| Namey | 1 | |
| Namez | 1 | |
| Class 2 total | 3 |
Showing all the 1s do not make sense and make the report hard to read.
Shall appreciate suggestion how to "not" showing the 1s.
If something like this
creating PivotTable add data to data model and create measure
Count Names:=IF( HASONEVALUE(Table1[Name]), "", COUNTROWS( Table1 ) )Use it in PivotTable instead of default aggregation.