millhouse123
Active Member
- Joined
- Aug 22, 2006
- Messages
- 335
I am new to Power Query and Power Pivot so I am not sure if this is even possible. I have a data set that has 3 buckets that sum book value and weighted duration. I was able to figure out how to create a custom column formula to calculate the weighted duration by bucket and source.
I created a pivot table based on this data which looks like this
Source Bucket Sum book value sum of WA duration
1st Source <4 106,479,948 5.16
1st Source 4-8 181,677,683 9.98
1st Source >8 284,681,654 5.44
1st Source Total 572,839,286 20.58
All of the above data is technically correct however the weighted average duration total should be 6.83 instead of 20.58 which is the weighted average of the total 1st source.
Any ideas how to get my data to show up this way would be great.
Thank you
Code:
=([Book Value]/
[SIZE=3][COLOR=#0000ff][SIZE=3][COLOR=#0000ff]CALCULATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=3]([/SIZE][SIZE=3][COLOR=#0000ff][SIZE=3][COLOR=#0000ff]SUM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=3]([Book Value]),[/SIZE][SIZE=3][COLOR=#0000ff][SIZE=3][COLOR=#0000ff]FILTER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=3](Append1,Append1[Source]=[/SIZE][SIZE=3][COLOR=#0000ff][SIZE=3][COLOR=#0000ff]EARLIER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=3](Append1[Source])&&Append1[Bucket]=[/SIZE][SIZE=3][COLOR=#0000ff][SIZE=3][COLOR=#0000ff]EARLIER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=3](Append1[Bucket])))*[Duration])[/SIZE]
I created a pivot table based on this data which looks like this
Source Bucket Sum book value sum of WA duration
1st Source <4 106,479,948 5.16
1st Source 4-8 181,677,683 9.98
1st Source >8 284,681,654 5.44
1st Source Total 572,839,286 20.58
All of the above data is technically correct however the weighted average duration total should be 6.83 instead of 20.58 which is the weighted average of the total 1st source.
Any ideas how to get my data to show up this way would be great.
Thank you