1 of 1 people found this helpful
You could either create a calculated field:
if not isnull([rating2]) then 1 else 0 end
or use count([rating2]) instead of sum([Number of Records]) - count ignores the nulls
I'm not quite understanding your goal. There are 18 rows in the file, 3 per value of rating. In the Sheet 1 you posted, the view has the rating dimension on Columns, so SUM(Number of Records) as the Label is accurate - 3 rows were used to calculate each value.
If you use the calculation Alex described, that will get rid of Null values, so rating =2 would return 2, etc. Nothing would return more than 3, so I'm confused about how you got a number of records = 5. Are you trying to count all the non-null values of rating2 and rating3 for each rating?
Also, when you are posting a workbook, please post a packaged workbook (.twbx), that way your data will be included in the workbook and it's easier to open.