cancel
Showing results for
Did you mean:
Creator II

## Proportion at Subtotal Level

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])

1 Solution

Accepted Solutions
Specialist

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

7 Replies
MVP

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

Specialist

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.

Creator II
Author

how would I "share a sample"?

Creator II
Author

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

Specialist

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

Creator II
Author

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])

Community Browser