Forum Discussion

DanielleMcGrath's avatar
DanielleMcGrath
Qrew Member
5 years ago

Incorrect percentage in reports

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
------------------------------
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    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
    ------------------------------