Pivot Table vertical and horizontal summing

[#1]

Hi there

I have a problem with the calculation in a pivot table.  I need to sum a column (MG Value) vertically, but instead it’s taking into consideration the other dimensions.  In my example here “% of Total MG Value” should sum up to 100% but instead it’s see’s two dimensions and dividing this between the two (49.73% + 50.27% = 100%).  Where both of them should have been 100% on their own.

Im attaching a reduced data model for you.

[kristoferahlin]

Hi,

Please see attached file. (You need to include also period in the TOTAL experssion)

Cheers,

Kristofer

[tresesco · Accepted solution]

A small correction in the expression would help:

sum([Total Portfolio Value]) / sum(TOTAL <MemberGroupName, PortfolioPeriod> [Total Portfolio Value])

[#4]

That is so awesome.  I cant why i never tried that.  Silly mistake.

But thanks a lot Kristofer.  You're the best !!!

[#5]

Thanks to you too Tresesco.  That solved my problem.  It means I can go home now (after a few days).

[#6]

Just a follow-up question please?  How do I get the other dimension (% of Total Fund Value) to add up to 100% at the bottom, instead of sharing it amongst the PortfolioYear dimensions.

[tresesco]

sum([Total Portfolio Value]) / sum(TOTAL  <FundName, PortfolioYear>  [Total Portfolio Value])

[#8]

I've tried that.  But it doesnt add up to 100% at the bottom. 

[tresesco]

It does with me. PFA

[#10]

It does add up to 100 if you only select 1 PortfolioYear.  But as soon as you select more than 1, it does not.