Discussions

Expand all | Collapse all

Incorrect percentage in reports

  • 1.  Incorrect percentage in reports

    Posted 05-14-2020 19:16
    Edited by Danielle McGrath 05-14-2020 19:17
    I am trying to create a report that calculates a percentage: [Field B] / [Field A] . 
    [Field B] is a numeric field and [Field A] is a formula-numeric field. 

    In the 4th column, it is calculating a percentage, but it's not correct and I can't figure out why. What I'm really needing is that total number at the bottom, what should come out to 1289/1560 or 82.62%. Can someone help me figure out what I am doing wrong?

    Date
    Field A
    (tot)
     Field B
    (tot)
    Formula (Numeric-Percent)
    B/A
    (avg)
     
    2009 7 5 80.00%
    2010 70 60 91.67%
    2011 57 50 93.00%
    2012 87 81 96.30%
    2013 106 70 73.68%
    2014 151 139 93.79%
    2015 120 102 90.00%
    2016 193 175 93.85%
    2017 166 140 89.31%
    2018 133 124 96.37%
    2019 357 244 76.94%
    2020 113 99 89.09%
    Totals (12 groups) 1560 1289 88.54%


    ------------------------------
    Danielle McGrath
    ------------------------------


  • 2.  RE: Incorrect percentage in reports

    Posted 05-14-2020 19:47
    The problem is that the column Average at the bottom is a simple average of the numbers in that column and not 1289/1560 = 82.62%

    So if you Produced 1000 widgets in 2019 with a success rate of 100% and in 2020 you only produced 2 widgets with a success rate of 50%, then it would average those two values for 75%.  but really the weighted Average is very close to 100%.   

    The only way to get the mathematically correct weighted average is to have a Parent table and summarize up the total A and the total B and do your calculation on that table.  I suggest that you turn off showing Averages on that last column as its better to be silent than to be wrong.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------