Averaging Results of two Serial Charts Totals

318
0
05-07-2021 09:37 AM
Caleb_Kaufmann
New Contributor II

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 IDTotal Market ValueOriginal Sales PriceRatioTrue Ratio
10100000400002.5 
10120000600002 
10150000700002.143 
1070000200003.5 
Total4400001900002.5382.316
201200001000001.2 
20140000600002.3 
20100000900001.111 
Total3600002500001.5371.44
3050000450001.111 
3085000600001.417 
Total1350001050001.2641.286
4075000700001.017 
40130000900001.444 
40120000500002.4 
40100000500002 
Total4250002600001.7151.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)?

0 Kudos
0 Replies