cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Choroszewicz_J
Regular Visitor

Best way to approach monte carlo simulations (dynamic calculated columns or multiple measures issue)

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? 🙂

1 REPLY 1
v-xuding-msft
Community Support
Community Support

Hi @Choroszewicz_J ,

 

Please refer to the link.

Monte Carlo Analysis using R and Power BI - Bing video

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.