Hello,
I've been struggling to find out solution for the problem I'm facing so I'm taking it to You guys for help 🙂
I'm trying to create a monte carlo simulation that would help forecast our team's delivery rate. What I have created and tried so far is this:
- I have Table 1 which consists of 1k+ rows. Each row is a simulated scenario that is based on our historical data about delivery rates. There are 10 columns, each one of them consists one historical value picked randomly (based on the frequency how often did this value came up in the past). Delivery rates are in a whole number format (this number represents the sum of the effort planned for each item that has been done during this period).
- I have Table 2 which reflects our planned work for the future - one column from this table is a numeric estimate of how big of a work is that. It's the same format that is being used to summarize in Table 1 simulation. These values then can be filtered by - let's say product category.
Now, what I want to do. My ultimate goal here is to provide a way to run a simulation where I can filter out Table 2 to specific category, sum the remaining estimated work for that filter and run a simulation based on Table 1 to create a cumulative probability diagram that will visualize when and how probable is the work being done.
What I've already did was this:
- I've created a calculated column for Average Delivery Rate for each scenario from Table 1
- I've tried to create a calculated column within Table 1 that would DIVIDE Estimated Remaining Work by Average Delivery Rate but calculated columns seems to be static only (it is not responding to filters so it calculates ALL the remaining work and not filtered out to the specific category that I chose).
- I've tried to calculate a measure but unfortunately measures are giving me only a single value.
So I suppose I need something like:
- creating a measure that would be calculated for each row in the Table 1
- count the frequency for each value calculated in this measure
- based on the frequency - count a % of total and running total of this % of totals
- based on that table - create a cumulative distribution chart
Any hints or ideas how can I approach that? 🙂
