Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
Oh I guess I've got it! Try to use "sum(total<Branch,BDM>" instead of "sum(total<BDM>".
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
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.
how would I "share a sample"?
do you have an example of how to do that in the script or point to a thread that references the idea?
Check these links out
Preparing examples for Upload - Reduction and Data Scrambling
Oh I guess I've got it! Try to use "sum(total<Branch,BDM>" instead of "sum(total<BDM>".
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])