Forum Discussion

jmcph's avatar
jmcph
Icon for Helper III rankHelper III
5 years ago
Solved

what if to affect selected values only

Hi i am creating a table visual that uses "What if" analysis as follows:      What i want is for the Increase / Decrease on the Forecast Qty Value to affect only the selected Category and le...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    jmcph , Make sure Category is an independent slicer

    measure =
    Var SalesQty = Sum ( Sales[Qty])
    Return
    calculate([SalesQty] * (1 * [Forecast Qty Value] ), filter(Table, Table[CATEGORY] = selectedvalue(CATEGORY[CATEGORY])))
    +calculate([SalesQty], filter(Table, Table[CATEGORY] <> selectedvalue(CATEGORY[CATEGORY])))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

jmcph , Make sure Category is an independent slicer

measure =
Var SalesQty = Sum ( Sales[Qty])
Return
calculate([SalesQty] * (1 * [Forecast Qty Value] ), filter(Table, Table[CATEGORY] = selectedvalue(CATEGORY[CATEGORY])))
+calculate([SalesQty], filter(Table, Table[CATEGORY] <> selectedvalue(CATEGORY[CATEGORY])))

  • jmcph's avatar
    jmcph
    Icon for Helper III rankHelper III
    5 years ago

    Hi amitchandak , what do you mean an independent slicer? 

     

    I created a separate tab to test the measure you provided but it still shows the selected category only. 

     

     

    Where could i have gone wrong? Or i have misinterpreted the measure you gave. 

     

    Still thank you for answering! 

     

  • jmcph's avatar
    jmcph
    Icon for Helper III rankHelper III
    5 years ago

    Yeah! This totally works! I just didnt understand at first on what you mean by independent slicer. 

     

    Thank you! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you so much! This was immensly helpful. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    What if you have an additional filter? 

     

    For example, what if you only want to adjust the forecast for the selected category and for only a selected region, How would you do that?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Nevermind amitchandak . I created a calculated table for that second filter and adjusted the formula below: calculate([SalesQty] * (1 * [Forecast Qty Value] ), filter(Table, Table[CATEGORY] = selectedvalue(CATEGORY[CATEGORY])))
      +calculate([SalesQty], filter(Table, Table[CATEGORY] <> selectedvalue(CATEGORY[CATEGORY]) || Table[CATEGORY] <> selectedvalue(CATEGORY2[CATEGORY2])