I am working on a dashboard that will need a dynamic calculation based on what the end user will select in a map.
I have data as such:
County Population Participating in Labor Force Employed Unemployed
County 1 200 150 100 50
County 2 100 75 50 25
County 3 150 85 60 25
I am trying to create a calculated field where the end user can select any combination of the above counties and return the following results:
(Sum of Selected Unemployed)/(Sum of selected Participating in Labor Force)
ie., a user selects County 1 and County 3 by clicking on them on the map.
I have duplicated the map as a crosstab, and I have successfully created fields that show the Sum of Participating in the Labor Force, and the sum of Unemployed. I added these to the crosstab and calculate using Table Down. However, I created a third calculation to divide Sum of Unemployed by the Sum of Participating in Labor Force, and compute using Table Down and my results are yielded as 0.
edit again: strangely, the correct number is showing up in the Tooltip but not in the actual window.
From what I can see here I would try
window_sum(sum(unemployed))/window_sum(sum(Participating in labor Force))
and then have the map work as a filter.