3 Replies Latest reply on Apr 6, 2016 1:19 PM by Ryan Colliver

Need help with a formula

Greetings, I need help with a formula - I'm having trouble wrapping my mind around it.  I'll explain the variables below, and any assistance is appreciated.

There are two variables

Vendors (This is a dimension, but I'm going to want a distinct count)

Spend (\$)

There is a spend figure associated with each vendor, and I want to find out the average number of vendors per \$1M of spend.

The entire spend is about \$88 million, and if needed, dividing by 88.12 gets the spend data into \$1M sets.

This might be elementary for some, but not getting it.

Thanks in advance for the help.

-Jocelyn

• 1. Re: Need help with a formula

Won't the average number per \$1M change based on which vendors you include in each \$1M set?  is there a rule for how you want that to be counted? For example, are you asking for average number of vendors per \$1M based on the date? and if so, what happens when the aggregation crosses over \$1M to be \$1.2M based on the last vendor that was added?

• 2. Re: Need help with a formula

If I correctly understand your issue, I would do the following...I am sure there is a more sophisticated way (just saw Jeffrey's memo pop up while writing this and he is far better at this than me)- this is just my simple straightforward approach.

Create a calculated field doing a distinct count of of vendor, then create a second calculated field, which takes the sum of your spend and divides by your new count of vendors field. Then you can do your division by 1,000,000.

Distinct Count of Vendors:

countd([vendor?]   we will call this calculation "count.vendor"

Spend divided by vendors PART A

sum([spend?])/ count.vendor

Complete the second calc by taking your spend divided by vendors calc and divide by 1,000,000, then regardless of the selection or attributes, you would arrive at your calc of vendors per \$1M of spend

PART B

(sum([spend?])/count.vendor)/1,000,000

• 3. Re: Need help with a formula

Following is an example that uses the metrics to which I was referring (I used your first name in the metric examples). You can download the file to see the details.