3 Replies Latest reply on Feb 6, 2019 7:34 PM by Shinichiro Murakami

    Creating an index against an average?

    Andrew Lloyd

      I thought this was going to be relatively easy. I have a table of orders counts and revenue for various customers. Average Order Value (AOV) = sum(revenue)/sum(ordercount). With the customers on the row shelf, this shows me the AOV per customer, perfect. Now I want to index each of these customers' AOV against the overall average. So if the overall Avg AOV = 100, and Customer 1's AOV = 90, the index would be 90/100 = .9.

       

      So here is my calculated field: (sum(revenue)/sum(ordercount))/{FIXED : sum(revenue)/sum(ordercount)}. But this gives me the error: Cannot mix aggregate and non-aggregate arguments with this function. I thought the LOD calc would just stand on it's own and produce a single value that I could use anywhere. Thoughts?