Is it possible to estimate how much a hurricane will cost before it strikes?

Goals: The first page of this report was aimed at helping a user decide how much money should be put in the budget on a yearly, monthly, storm, and State level.

The second page was an attempt to guide a user in establishing how much difference in expense will occur if the hurricane changed categories.

Damage ($) Report Page Strategy:

At first, I was going to attempt to estimate the storm damages between multi State storms. A multiplier was going to be established from using the medians of the storms that affected only one State. However, the sample sizes were not large (<30) and not all States were involved with a storm of their own.

When a hurricane hit multiple States, the data failed to separate damages suffered by each State. Only a total for damages was provided. After realizing the lack of data to do my first idea, I decided to keep it at the level of Damage/Storm and Damage/State. This was the best breakdown I could do without disrupting the data or give the data a false meaning.

This new approach required my code to determine whether to report damages on the Storm level or State level. Maximum and minimum damages were added on the State level to give context to the severity of the particular storm being viewed.

Year, Month, Storm and State levels:

Year and Month levels: Year, Month and State levels:

Damage ($) Comparison Page

Having small sample sizes it is hard to determine if damages from a particular category hurricane would follow a normal distribution Therefore, I decided to calculate the confidence interval for the median to compare it to the average, which is affected by assumed outliers shown in the Distribution chart. This way, weight can be given to the credibility of the Average Difference Confidence Interval calculations that are assuming a normal distribution will occur as more data is presented.

To help further give credibility to the average difference, I decided to calculate at which Confidence Level was the data significant to consider the difference between the averages. At first, I thought of using parameters that the user could use to change the Confidence Level, then I decided to use a for while loop. DAX does not have a for while loop, but I was able to use a work around Greg Deckler posted on YouTube.

Here is a link to the working model: Hurricane Damages