In order to solve your problem I used data blending. In fact this is probably the easiest method in this case. Data Blending allows values to be aggregated and then linked together. In this case I will be summing up your amounts and then linking them from the created sheet's create date to the sold sheet's closed date.
First Get things Set Up Right
1) I first connected to your excel sheet and then only chose the created table. Rename this datasource CreatedData
2) I connected to another data source and only chose to use the sold table. Rename this data source CreatedData
3) I selected the data menu from the ribbon and went to edit relationships.
4) I chose my CreatedData as the primary and SoldData as secondary
5) I then selected the custom radial selection and removed everything
6) I added in a relationship from created from created datasource to closed from sold data source.
Now to Build the Table!
1) I right-click and dragged your created data - from the created data source to the rows shelf
2) Right-Click Amount in this datasource and rename it 'Created'
3) Drag that to text
4) Go to the Sold datasource and rename amount to 'Sold'
5) Drag that field onto the text inside the data table you've built already.
6) Return to your primary datasource and Create a calculated field so that the SUM([SoldData.Closed]) / SUM([CreatedData.Created])
7) Drag that field onto your measure values card
I've attached a workbook to assist you with this.
Blending Example.twbx 30.4 KB
Thanks, you got me 90% there. Blending the data is the way to go, just was having trouble figuring how to link them.....
Question if you know. I have to put in another piece of criteria. I need to filter on PRODUCT (which I actually created groups for but after playing around it doesn't appear I can use them in formulas?)--but where the group product =Supply in EITHER the primary (created) or secondary source (sold).
Is my only luck building an if or statement on the ungrouped PRODUCT and then putting that in my filter? How can I do "OR" statements between the two that I grouped (or maybe the answer is that I cannot?)
Never mind! Figured it out. I need to choose MY (month year) when data blended and not the full date. I also need to pull BOTH the Products into the filter view. Phew. Took me a while. Thanks for your help.