Hello,
I work for a County GIs department and we are currently creating an Operations Dashboard for the Assessors office. They want to display the ratio between the average of the total market value and the average of the original sales price of a property for each class designation. I have provided an example below. The numbers are completely made up as the real data has approximately 73,000 rows. The Total of each class's values, in bold is divided to create the True Ratio in Green. However, ArcMap and Excel both calculate the Red number because it is pulling the average from the ratio attribute field and dividing by the total entries. Obviously this is causing a Paradox of trying to average the average which is the wrong answer.
| Class ID | Total Market Value | Original Sales Price | Ratio | True Ratio |
| 10 | 100000 | 40000 | 2.5 | |
| 10 | 120000 | 60000 | 2 | |
| 10 | 150000 | 70000 | 2.143 | |
| 10 | 70000 | 20000 | 3.5 | |
| Total | 440000 | 190000 | 2.538 | 2.316 |
| 20 | 120000 | 100000 | 1.2 | |
| 20 | 140000 | 60000 | 2.3 | |
| 20 | 100000 | 90000 | 1.111 | |
| Total | 360000 | 250000 | 1.537 | 1.44 |
| 30 | 50000 | 45000 | 1.111 | |
| 30 | 85000 | 60000 | 1.417 | |
| Total | 135000 | 105000 | 1.264 | 1.286 |
| 40 | 75000 | 70000 | 1.017 | |
| 40 | 130000 | 90000 | 1.444 | |
| 40 | 120000 | 50000 | 2.4 | |
| 40 | 100000 | 50000 | 2 | |
| Total | 425000 | 260000 | 1.715 | 1.635 |
So my main questions are the following:
Is there a way to display the correct average between the two totals of each Class or all classes on Dashboard with live updating and interaction of other filters changing the data?
Can Dashboard (with a lot of filters) correctly display the true ratio on a line graph using the two separate serial charts that display the averages of total market value and original sales price when filtered by Class Id?
Is there a way to calculate the real ratio in ArcMap Pro/Desktop on the same attributes and be able to upload it with the original data?
Or, would it just be easier if I extract the data to a separate document and run multiple quarries and field calculations and upload that layer/table as a separate item on dashboard (this defeats dashboards interactive capabilities and would require a daily script to run that extracts and does the full calculations)?
