Hello,
I have following excel file that is being used to build geo map Tableau dashboard and "Final score" is used to color code market name (below 60 high risk, 60-80 medium risk, above 80 low risk etc) Currently moving average per month and other fields are being calculated in excel but would like to do these calculations in Tableau, but cannot use Tableau's table calculation as I am using only Final score in the geo map. please shed light on how that can be done. I am trying to create "Final Score" column from excel which is calculated as follows:
1) moving month over month percentage changes calculated for each "Market Name", I have 400+ market names in my file
2) then average of "moving month over month percentage change" for all months for each market name
3) then "Final Score" is calculated by (1+ result of Average of Month-Month change) *(latest score for the market)
| Market Name | Latitude | Longitude | Score | Report_Date | Moving_Month_Over_Perc_Change | Average of Month-Month Change | Final Score |
| Chicago | 43.04103 | -76.2714 | 68 | 7/1/2018 | | | |
| Chicago | 43.04103 | -76.2714 | 72 | 8/1/2018 | 6% | | |
| Chicago | 43.04103 | -76.2714 | 56 | 9/1/2018 | -22% | | |
| Chicago | 43.04103 | -76.2714 | 62 | 10/1/2018 | 11% | | |
| Chicago | 43.04103 | -76.2714 | 67.5 | 11/1/2018 | 9% | | |
| Chicago | 43.04103 | -76.2714 | 76.7 | 12/1/2018 | 14% | | |
| Chicago | 43.04103 | -76.2714 | 78 | 1/1/2019 | 2% | | |
| Chicago | 43.04103 | -76.2714 | 70.7 | 2/1/2019 | -9% | | |
| Chicago | 43.04103 | -76.2714 | 68 | 3/1/2019 | -4% | | |
| Chicago | 43.04103 | -76.2714 | 90 | 4/1/2019 | 32% | | |
| Chicago | 43.04103 | -76.2714 | 66 | 5/1/2019 | -27% | | |
| Chicago | 43.04103 | -76.2714 | 64.5 | 6/1/2019 | -2% | | |
| Chicago | 43.04103 | -76.2714 | 79.5 | 7/1/2019 | 23% | 3% | 81.62406527 |
1 of 1 people found this helpful