2 Replies Latest reply on May 11, 2016 1:16 PM by Doug Stanley

# Help with an advanced(?) calculation

Here's my situation:

I have 1,085 customers in my data. I need to compute an average for the subset of customers who have spent \$1,000 or more. BUT ... I need to show all 1,085 customers compared to the average computed for the 126 customers who have spent at least \$1,000.

I get part way there with a FIXED calc that correctly computes the average for those who have purchased at or above the \$1,000 threshold, but it also computes a second average for those customers falling below the threshold. I don't want the second average to be computed, rather I want it to display the average the was calculated off the subset of customers spending above the \$1,000 threshold. Here's a snip:

Thanks,

Doug

• ###### 1. Re: Help with an advanced(?) calculation

You do not really need a set here. You can write a calc like:

{sum({ fixed [Customer] : avg(if [Sales] > 1000 then [Sales] end )})} / {sum({ fixed [Customer] : countd(if [Sales] > 1000 then [Customer] end )})}

The numerator throws a value of 548,472  (sum of sales of all customers who have 1000 or more) on all rows and the denominator throws a value of 126 (number of customers who have more than 1000 sales value)

Hope this helps!