Proportion at Subtotal Level

[carlcimino]

Hello, I have a Pivot Table chart object that looks like the picture below.  I have three metrics that break the total by Segment.  Here is the Set Analysis I have to calculate the proportion at the BDM level.  My issue is that the Subtotals at the Branch/Region and overall total do not show the same sum to 100% as the BDM level.  How would you accomplish that?  Any help would be much appreciated.

Thanks,

Carl

sum({<YEAR={$(=max(YEAR))},SEGMENT={"Portfolio"}, [New HH Count]={1}>}[Policy Total TPREM])
  /
  sum(total<BDM>{<YEAR={$(=max(YEAR))}, [New HH Count]={1}>}[Policy Total TPREM])

[sunny_talwar]

I think you might have to use some form of How to use - Dimensionality() to get this working.... If you can share a sample... we might be able to have a look at this

[Sergey_Shuklin]

Hello, Carl!

One of the most painless way is simply create a calculation within the script which will make additional rows for level BDM which will contain the aggr total sum. It's some kind of hand-made total =D .. Why not.

[carlcimino]

how would I "share a sample"?

[carlcimino]

do you have an example of how to do that in the script or point to a thread that references the idea?

[sunny_talwar]

Check these links out

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[Sergey_Shuklin · Accepted solution]

Oh I guess I've got it! Try to use "sum(total<Branch,BDM>" instead of "sum(total<BDM>".

[carlcimino]

Rock on Sergey.  The below returned what I needed.  Thank you.

sum({<YEAR={$(=max(YEAR))},SEGMENT={"Portfolio"}, [New HH Count]={1}>}[Policy Total TPREM])
  /
sum(total<Branch, BDM, Region>{<YEAR={$(=max(YEAR))}, [New HH Count]={1}>}[Policy Total TPREM])