Divide a Row Value by Maximum Column Value

[ben2r]

Dear all, I am trying to create a calculation that divides a row value in a table by the maximum value within that column.

My table looks like this:  

Category	Time	Calls	Max Calls
Discussion	2017: Jan	761	-
Discussion	2017: Feb	832	-
Discussion	2017: Mar	897	-
Discussion	2017: Apr	618	897
Discussion	2017: May	808	-
Discussion	2017: Jun	813	-
Discussion	2017: Jul	663	-
Discussion	2017: Sep	727	-
Discussion	2017: Oct	763	-
Discussion	2017: Nov	704	-
Discussion	2017: Dec	574	-
	Total	8,160	897

Calls is a variable:

COUNT(DISTINCT [Call ID])

Max Calls is a variable:

AGGR(MAX(AGGR(COUNT(DISTINCT [Call ID]),[Created Month])),category)

What I am trying to do is divide each row by the maximum value. I.e. based on above, row 1 would be 761/897. 

I cant work out how to calculate this maximum value for the table then use it across all values. Any other way I have tried leads to the max value being the same as the count of calls. 

Thanks,

Ben

[sunny_talwar · Accepted solution]

Try one of these

AGGR(NODISTINCT MAX(AGGR(COUNT(DISTINCT [Call ID]),[Created Month])),category)

or

MAX(TOTAL <category> AGGR(COUNT(DISTINCT [Call ID]),[Created Month]))

[ben2r]

Excellent - Sunny my hero once again. 

I have been struggling with this for ages. I was not familiar with the no distinct command!