
1. Re: Need help with a formula
Jeffrey Shaffer Apr 6, 2016 8:56 AM (in response to Jocelyn Gibson)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
Ryan Colliver Apr 6, 2016 9:11 AM (in response to Jocelyn Gibson)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
Ryan Colliver Apr 6, 2016 1:19 PM (in response to Jocelyn Gibson)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.