Hi all,
I'm running Windows 7 64bit, with Excel 2010 and the Powerpivot plugin. I'm new to Powerpivot and I feel like I'm really close to getting it to do what I want but I can't seem to figure out the last step. I have a dataset roughly 50k lines long formatted like this.
<tbody>
</tbody>
Each line is representative of one paid invoice. What I'm trying to do is create a weighted average of the "Days Past Due" column for each individual account.
Currently the way I do this (one account at a time) is subtotal the "Amount" column.
Then divide each individual invoice for an account by the subtotal for that account (giving me the weighting I want to use).
I then multiply this weighting by each individual "Days Past Due" entry (in a helper column), and finally take the sum of that column.
This gives me the weighted days past due for an individual customer.
Now I want to do this for every customer in our database at once.
I've been able to have the pivot table generate this weighting for me using the "Show values as a % of parent Row" option. However I am not able to create a measure that multiplies the "Days past due" by this implicit value.
I suppose what I'm looking for is how do I create a measure that will divide an individual invoice's amount by the subtotal for that account?
I've been trying to use this guide (Weighted Averages: Another Use of SUMX() « PowerPivotPro) but I seem to be missing something.
Apologies if I've been unclear. If necessary I can upload a dataset to better demonstrate what I'm trying to do.
Thanks in advance for any help!
I'm running Windows 7 64bit, with Excel 2010 and the Powerpivot plugin. I'm new to Powerpivot and I feel like I'm really close to getting it to do what I want but I can't seem to figure out the last step. I have a dataset roughly 50k lines long formatted like this.
| Account | Days Past Due | Amount |
| A | -2 | 2000 |
| A | -1 | 4000 |
| A | 15 | 5200 |
| B | 12 | 400 |
| B | 14 | 102 |
| C | 10 | 485 |
| C | 21 | 1039 |
| C | 10 | 1394 |
| D | 4 | 583 |
<tbody>
</tbody>
Each line is representative of one paid invoice. What I'm trying to do is create a weighted average of the "Days Past Due" column for each individual account.
Currently the way I do this (one account at a time) is subtotal the "Amount" column.
Then divide each individual invoice for an account by the subtotal for that account (giving me the weighting I want to use).
I then multiply this weighting by each individual "Days Past Due" entry (in a helper column), and finally take the sum of that column.
This gives me the weighted days past due for an individual customer.
Now I want to do this for every customer in our database at once.
I've been able to have the pivot table generate this weighting for me using the "Show values as a % of parent Row" option. However I am not able to create a measure that multiplies the "Days past due" by this implicit value.
I suppose what I'm looking for is how do I create a measure that will divide an individual invoice's amount by the subtotal for that account?
I've been trying to use this guide (Weighted Averages: Another Use of SUMX() « PowerPivotPro) but I seem to be missing something.
Apologies if I've been unclear. If necessary I can upload a dataset to better demonstrate what I'm trying to do.
Thanks in advance for any help!
