Total that is different from sum of parts

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.
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Millhouse,

If I am understanding your model correctly, 'WA duration' is a calculated column in your 'Append1' table and you used that column directly as a Value Field in your pivot table ( explaining the 'Sum of WA duration' column ). I believe what you need is a Measure instead of a Calculated Column. That way your total doesn't have to be one of the regular pivot table aggregating functions (Sum, Count, Average, etc.) and you can have the same calculation as in each row or an entirely different if needed be (maybe using ISFILTERED).

Rob Collie at PowerPivot(Pro) has a great post on When to Use Measures vs. Calc Columns that might enlighten you regarding this topic. It is a very common mistake to create Calc Columns where Measures would work better, and most of us Excel users have made that mistake when first using Power Pivot.

If you can't solve the issue after reading the article, let me know and I will be glad to help.

PS. A small sample of your data would be very handy in helping you find a solution.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top