Two separate slicers for same column with different selections

[Anonymous]

Hello,

I am trying to create a what-if analysis where a user can select one or more products, and modify their sales price and quantity to see how this would affect revenue. However, since changing the price of a product might also affect the sales of other products, I would like to allow for a user to independently select a second group of products, modify these too with different parameters, and then create a total of the two groups. (A scenario could be that if the price of product A is lowered, some people are expected to switch to this from Product B. So while there will be an increase in the sales of Product A, the sales of Product B will decrease - would the price adjustment overall still be worth it?)

I have one big sales transaction table that contains (among others) a sales date column, a product name column, and a total price column. My idea was to duplicate the product column and add a separate slicer to the report for each to allow for the selection of two independent product groups. The issue is of course that making a selection in one slicer will also filter the other, although I was able to get them to allow different selections by setting them not to interact with each other. Similarly, by having a KPI interact with only one of the two slicers, I can get the subtotals for each group. The problem occurs when I attempt to add the two groups together to get a total - this cannot be done just by editing the interactions. My knowledge of DAX is not that great but I have tried (without the what-if parameters) things like:

Sales = 
CALCULATE(SUM(FACTS[Amount]), ALLSELECTED(FACTS[Product Column A]), ALL(FACTS[Product Column B])) 
+ CALCULATE(SUM(FACTS[Amount]), ALLSELECTED(FACTS[Product Column B]), ALL(FACTS[Product Column A]))

But this does not work. Is what I am looking for at all possible? If it requires a completely different approach, I would be more than happy to try...

Thanks in advance!

[Anonymous]

I am slso stuck at something similar like this. Please let me know if you find a solution to this.

[Anonymous]

Also having same issue - was a solution found for this?

[v-yulgu-msft]

Hi @Anonymous,

Would you please share some sample data to help me better understand your requirement? Besides, if possible, could you illustrate desired output with graphs?

Regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msftThank you for your response!

I have uploaded a .pbix file here with a simplified example of what I am looking for. It contains one table with two columns (product and quantity) and one report page. The report page has two what-if parameters, two slicers and three cards. Both slicers are set on the same product column.

I would like to be able to make a different a product selection in slicer 1 and slicer 2 (like I for example did in the sample file), then have the three cards show the following numbers:

1. The total quantity for the products selected in slicer 1 times the selected slicer 1 multiplier.
2. The total quantity for the products selected in slicer 2 times the selected slicer 2 multiplier.
3. The sum of the above two. (that is, the total quantity for the products selected in slicer 1 times the selected slicer 1 multiplier plus the total quantity for the products selected in slicer 2 times the selected slicer 2 multiplier).

I have been able to get card 1 and 2 to show the correct totals by disabling some of the interactions with other visuals, but I can't get card 3 to work (either through measures or through adjusting the interactions). I'm not sure if there even is a (simple) solution for this, but any ideas would be appreciated!

Kind regards,

Stefan