Forum Discussion
Pivot Table Count Unique or Similar Values
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.
Sergei, thank you. I am a newbie in pivot table. I figured out to enable Power pivot and created the new measure created; but have difficulty in locating how to insert the measure field into the pivot table. I have looked up from ms excel forum, and https://www.howtoexcel.org/summarizing-text/, but could not find the proper way to insert the newly created measure. Please advice.
SergeiBaklan that's what I thought. Since it did not show up in the pivot table, I suspect I created it under a wrong table. I'll see if I can figure out how to recreate under the right pivot table.