# Making charts show exactly 100%

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.

100 pct Sample.qvw (183.0 K)