0

I have data like this

Person Total Count
First 5
Second 6
Third 5
Null 6

I want to take the nulls, divide by 3 (number of people), and add that number to each of the [Person]'s [Total Count] column. So, I want it to look like this:

Person Total Count
First 7
Second 8
Third 7
Null 6
Ken White
  • 123,280
  • 14
  • 225
  • 444
Joe Johnson
  • 73
  • 1
  • 1
  • 10
  • In your raw data, do you have 5 records with the value "First" in the "Person" column? Or do you have 1 record with the value "First" in the "Person" column and the value 5 in a column named "Total Count"? – Alex Blakemore Jul 23 '21 at 19:17
  • good question - It's the first option. "First" appears 5 times in the raw data – Joe Johnson Jul 23 '21 at 19:30

1 Answers1

0

Try something like the following on some shelf with Person on Rows

MIN( { SUM(INT(ISNULL([Person]))) / COUNTD([Person]) } ) + Count([Person])

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • I've run into this problem before but doing this gives me the error (while typing this as a calculated field) that I "can't mix aggregate and non-aggregate arguments with this function" – Joe Johnson Jul 27 '21 at 09:16
  • Try the revised answer – Alex Blakemore Jul 27 '21 at 12:08
  • hmm I think we're getting somewhere. Now, I am getting results in the "Total Count" column (before I was getting 0's there with only the null row populating the number of nulls). However, the numbers are off in the Total Count column. I – Joe Johnson Jul 27 '21 at 13:03
  • if you revise the post to show the actual raw data and the values you are getting for Total Count, then it might be possible to give feedback. However "the numbers are off" is a bit too vague to respond to usefully. – Alex Blakemore Jul 27 '21 at 16:47