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.

    PctofTotal.PNG

     

    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.