dynamic scenario modeling

[Anonymous]

Hello, 

I am looking to use Power BI model total costs and by product over time whereas the user can select (using slicers?) the product and year to begin purchasing for that product.  I also want the model to auto-calculate a progressive annual cost increase of 20% of the total cost by year (i.e. 20% in year 1, 40% in year 2, etc.. until 100%, at which point the costs would stablize).  

Could anybody help point in me in the right direction in terms of a good approach?  

Thanks much.

Robert  

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Based on current description, it's hard to imagine your data model and not clear about what you were trying to achieve. Please provide some dummy data so that we can reproduce your scenario and show us your desired result with examples. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Anonymous]

Dear Yuliana Gu: 

Thanks for your reply. I want to model different cost scenarios that vary following the year each new product is introduced. I expect this would be calculated based on 3 source variables which I will calculate seperately. 

Implementation rate =

0% (no implementation)

20% (introduction year)

40% (year2),

60% (year3)

80% (year4)

100% (year5+)

Year =

2019

2020

2021

2022

2023

2024

2025

Total Product1 Cost

Product1 $1,000,

Product2 $5000

Product3 $800.   

As a result I would like to generate a stacked column chart (by product) that shows the total cummulative cost on the y-axis and year on the x-axis.  I would like to enable dynamic control (using slicers) for all 3 vars.   For example, if we choose to introduce only Product1 in Year 2019, the chart would show $200 ($1,000 * 20%).   Then in Year 2020, we add Product2, show the stacked column for that year would show Product1 $400 ($1,000 * 40%) + Product2 $1,000 ($5,000 * 20%).  Then in Year 2021, if we add Product3 we would see Product1 $600 + Product2 $2,000 + Product3 $160.  Etc, etc….  Thanks much for any guidance you can provide.

Robert