Hello,
I am trying to create 2 scenario side by side, so users can dynamically choose the values of the slicer to see the impact of each scenario. My main table has 10 products, and each product has different vendors, and differnet price for each product, each vendor.
The end goal is that each product (product 1-10) has to have one vendor selected to get the final cost of whole product/vendor selection mix for the scenario comparison. Each scenario has 1 slicer (concatenate of product and vendor name). The users will get to choose which vendor the product the users want to assign it to, and see the cost impact.
For example, under slicer 1 for scenario 1, if the user chooses
product 1, vendor1,
product2, vendor2,......
then I will have a [total cost measure 1] that is the sum of total cost based on the product/vendor mix selection that the user chooses from slicer 1
same with slicer 2 for scenario 2. If the user chooses
product 1, vendor3,
product2, vendor4,....
then I will have a [total cost measure 2] that is the sum of total cost based on the product/vendor mix selection that the user chooses from slicer 2
I will then have a price diff = [total cost measure 2] - [total cost measure 1] to see the cost impact between 2 scenarios. (the cost impact of differnet product/vendor mix selection chosen)
What I have done so far:
new table selection 1 = values('main table'[product,vendor name])
new table selection 2 = values('main table'[product,vendor name])
[total cost measure 1] = calculate('main table'[Total Cost],filter('main table','main table'[product, vendor name] IN {selectedvalue('Selection 1'[product,vendor name])}))
When I select 1 value from slicer 1, the [total cost measure1] is showing the right total cost. However, if I choose more than 1 value from slicer 1, the [total cost measure1] shows blank
Q1) Am I going down the right route? If not, what's the best way to go about this?
Q2) How should I fix [total cost measure1] so that it calculates the sum of all product,vendor name values chosen in the slicer
Thank you so much!
