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 -


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



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


    vOffage -


    this is calculating how far from 100% we are


    Table Expression Sample -



    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.