Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Two separate slicers for same column with different selections

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!

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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's avatar
      Anonymous
      Not applicable

      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

  • Anonymous's avatar
    Anonymous
    Not applicable

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

    • Anonymous's avatar
      Anonymous
      Not applicable

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