A common problem where I work is that when we show a total value the individual components must visibly equal the total. I've seen questions about this situation before but never seen an acceptable solution. To eliminate my messy code I decided to use parameter variables to create a reusable solution.

Another requirement is that I show the data both in a columnar format as well as a traditional chart like a Pie Chart. So my variables allow for you to use them for both solutions. It does not require 2 sets of variables.

I will continue to improve this over time as I did get to a point where my current requirements are satisfied and have to move onto other projects.

1. Right now the true measure calculation SUM(VALUE)/SUM(TOTAL VALUE) is hard coded into the variables and I'll get that abstracted out so if a document has multiple formulas we don't need multiple sets of variables.

2. For the Pie charts I was having difficulty putting the rank into a variable allowing the set analysis filter to be passed in so that code is currently in to chart itself. I'll get that into a variable as well when I have time.

Here are some of the variables in case you don't want to download the qvw

vRank - Only(TOTAL {<[$2] = {'$1'}>}Aggr(Rank( Sum( FABS(VALUE))), [$2]))

vRank is only used in the columnar chart. I'll work on getting this functional for the pie chart later.

$1 is the column name and dimension value

$2 is the dimension name

vStastics -

NUM(ROUND($(vPctofTotal($1,$2)),.0001)

- IF($(vOffage($2)) > 0 AND FABS($(vOffage($2)))>=$(vRank($1,$2)),.0001,0) + IF($(vOffage($2)) < 0 AND FABS($(vOffage($2)))>=$(vRank($1,$2)),.0001,0) ,'#,##0.00%')

This is the guts of the solution. vPctofTotal is getting the primary rounded value that is "Incorrect". If vOffage is > 0 and >= the rank of the current value we subtract .0001 from the value. If we are below 100% then we add to the highest rank values.

vPctofTotal -

ROUND(SUM( IF([$2]<>'$1' AND [$2] <> $3 ,0,VALUE)),.0001)/ SUM( TOTAL VALUE)

This is doing the primary calculation of the percentage.

[$2]<>'$1' is checking to see of the dimension matches the string value passed in for the Columnar soution

[$2] <> $3 is checking to see if the dimension matches the dimension value passed in for the Pie Chart solution.

vTotalofPct -

SUM(TOTAL ROUND(AGGR(SUM( VALUE), [$1]) /SUM(TOTAL VALUE),.0001))

This one is simply getting the rounded total of all values at the chosen dimension ($1)

vOffage -

ROUND($(vTotalofPct($1))-1,.0001)*10000

this is calculating how far from 100% we are

Table Expression Sample -

=$(vStatistics(M,DIM2))

M is the dimension value we are calculating for and DIM2 is the dimension

Pie Chart Expression sample -

=ROUND($(vPctofTotal(String,DIM2,[DIM2])) ,.0001)

- IF($(vOffage(DIM2)) > 0 AND FABS($(vOffage(DIM2)))>=Rank( Sum( FABS(VALUE))),.0001,0)

+ IF($(vOffage(DIM2)) < 0 AND FABS($(vOffage(DIM2)))>=Rank( Sum( FABS(VALUE))),.0001,0)

This should be very similar to vStastics above. The main difference is that I'm doing the vRank calculation here.

## Comments