Been struggling with this problem for a few weeks now so I thought i would reach out for some help. What I am looking to do is create a calculated field that is the index of a brands sales to the average sales per geographic region. In this case DMA.
What I want to show is how much a brands sales over / under indexes in each DMA based on the category average in that DMA. So for example if Brand 1 has sales of 1000 in zip code 1 and the category average in the zip code is 2000, the index will be 50 (1000/2000)*100
I can achieve this calculation in a table (See attached Index Table) by computing the index calculation using by Pane, but my problem is, I can't do this with a map. If i compute using by DMA, it will use the full average across all DMAs and not within the DMA itself.
Any way I can achieve this calculation with a zip code?