Scenario Building using What if parameter

[kxj54590]

Hi folks,

I am working on a task which has motor vehicle data that has two datasets : one is input, second is tier range. I wanted to see the impact of my product rate using the tier range values that gets classified into tier level using what if parameter/scenario analysis. 

For example in the below data to the product "ABC" the current rate is 51% that gets tier level 2 assigned. This level is categorized on the basis of tier ranges given in another table as 2 tier range is 48% and 3 tier range is 72%, as our current rate is 51% which falls between 48% & 72% thats why classified under as "Tie 2". I wanted to dynamically create a visual using what if scenario. 

Please guide me on this using what if parameter. If not, is there any other way to achieve, will be able to accept that solution as well. 

Dataset:Tie range

Product ID	Product Name	2 Tier Range	3 Tier Range	4 Tier Range	5 Tier Range
B01	ABC	48%	72%	75%	96%
B02	BBC	45%	73%	82%	93%
B03	CDE	70%	82%	95%	99%
B04	EFG	24%	58%	80%	95%
B10	FSA	17%	52%	66%	93%
B11	PA	40%	69%	79%	95%
B12	OP	30%	60%	72%	81%
B13	EE	49%	55%	69%	85%
B14	KDM	90%	94%	97%	99%
B15	CSB	49%	68%	78%	97%
B16	CAD	47%	66%	72%	80%
B17	RAM	64%	75%	80%	91%
B18	ROM	45%	69%	78%	92%
B19	YZE	11%	15%	8%	5%
B20	SCD	60%	73%	79%	83%

Dataset: Input

State	Product ID	Product Received Year	Product Manufactured Year	Product Name	Tier Level	Rate
BC	B01	2020	2019	ABC	2	51%
BC	B02	2020	2019	BBC	4	87%
BC	B03	2020	2019	CDE	3	88%
BC	B04	2020	2019	EFG	4	80%
BC	B10	2020	2019	FSA	3	61%
BC	B11	2020	2019	PA	3	72%
BC	B12	2020	2019	OP
BC	B13	2020	2019	EE	5	95%
BC	B14	2020	2019	KDM	4	97%
BC	B15	2020	2019	CSB	2	66%
BC	B16	2020	2019	CAD
BC	B17	2020	2019	RAM
BC	B18	2020	2019	ROM	2	65%
BC	B19	2020	2019	YZE	3	19%
BC	B20	2020	2019	SCD	5	85%

Thanks

Shiv

[parry2k · Accepted solution]

@kxj54590 everything is dynamic in this solution, as data changes, everything will work accordingly.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@kxj54590 solution is attached, there are two tables, TierInput and TierRange and in the TierInput table, I added a new column for Tier

Tweak the solution as per your needs.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[kxj54590]

@parry2k i think you have unpivoted the columns for %rate in tier range correct ? The %rate is static values, however it should be dynamic, like a bin that should fall under those tier ranges given dynamically.  How can i achieve that? For eg: if i select a product with certain rate 40% for the year 2020, the tier is 3, what if i change my rate to the next year from 40 to 80% then it should dynamically give tier 5 for that particular product .