Jun 17 2021 11:20 AM - edited Jun 17 2021 11:22 AM
Jun 17 2021 02:00 PM
Jun 17 2021 03:03 PM
It's hard to give anything but general advice given the very sparse description of your situation.
One thing you should do, if you haven't already, is to look into the still relatively new UNIQUE function, which can extract from a long list the actual unique values, which can then be counted.
IF the aberrations are always following the variations in the example you gave, you could always look just at the first five characters, making use of the LEFT function to strip off the GL109 from "GL109--anything else" But if that was just an example and not representative of your actual data, you'll need to do something else to clean up the aberrations.
Here's a helpful video on UNIQUE: https://www.youtube.com/watch?v=9I9DtFOVPIg
Jan 10 2022 06:25 AM - edited Jan 10 2022 06:28 AM
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.
Jan 10 2022 06:52 AM
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.
Jan 10 2022 03:12 PM - edited Jan 10 2022 04:15 PM
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.
Jan 11 2022 02:28 AM
Jan 11 2022 08:35 AM
@Sergei Baklan 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.
Jan 11 2022 08:42 AM
Jan 11 2022 09:58 AM
Jan 11 2022 11:27 AM
You shall have some name which is source for PivotTable. It could be Table or Range, doesn't matter.