Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

switch measure with multiple select slicer data

Hi, I have a slicer "D_1_Deck" with four items as below: AMS APJ EMEA Renewal The four items are sales territories but for different product types: AMS, APJ and EMEA are for New products, Renewal is for Renew products. I am using Selectedvalue function to take the user's choice: Selected Deck = SELECTEDVALUE(D_1_Deck[Deck]). This Selected Deck was used by the following formula: Sales Amount = SWITCH(true(), [Selected Deck] in {"AMS","APJ","EMEA"}, [Total Sales Amount New Product], [Selected Deck]="Renewal",[Total Sales Amount Renew Product], BLANK()) Due to the restriction of Selectedvalue function. the user now can only make one choice out of the four slicer items. I want to allow the user to make multiple choices (any combination of the four items) so that the Sales Amount will calculate the sum of all the choices the user make. Thanks for help. PBISean
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    How about this measure:

    Sales Amount = 
SWITCH (
    TRUE (),
    MAX(Sheet3[cate]) IN { "AMS", "APJ", "EMEA" }, [Total Sales Amount New Product],
    MAX(Sheet3[cate]) = "Renewal", [Total Sales Amount Renew Product],
    0
)

     

    What the expected output should be when i select "Renewal" & "APJ" & "EMEA"?

     

    Best Regards

    Maggie

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Hi, I have a slicer "D_1_Deck" with four items as below: AMS APJ EMEA Renewal The four items are sales territories but for different product types: AMS, APJ and EMEA are for New products, Renewal is for Renew products. I am using Selectedvalue function to take the user's choice: Selected Deck = SELECTEDVALUE(D_1_Deck[Deck]). This Selected Deck was used by the following formula: Sales Amount = SWITCH(true(), [Selected Deck] in {"AMS","APJ","EMEA"}, [Total Sales Amount New Product], [Selected Deck]="Renewal",[Total Sales Amount Renew Product], BLANK()) Due to the restriction of Selectedvalue function. the user now can only make one choice out of the four slicer items. I want to allow the user to make multiple choices (any combination of the four items) so that the Sales Amount will calculate the sum of all the choices the user make. Thanks for help. PBISean
  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

     

    What do you want to do exactly? Can you explain it a bit more? For instance, what would happen if the user selects "AMS", "APJ"  and "Renewal"?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi AIB,

       

      Thanks for replying. 

       

      Yes, I want to allow users to multi-select from the slicer, and then my formula of total amount of sales will calculate the sum of all the sales for the items the user selected. 

       

      For example, user should be able to select AMS and APJ and EMEA, the slicer will pass the 3 item selection to the Total Amout of Sales formula and the formula will calculate the sum of total sales of AMS, APJ and EMEA. 

       

      Currently, the Selectedvalue canl pass only ONE choice to the Total AMout of Sales formula. 

       

      Thanks again for help.

       

      PBISean

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

         

        Ok but what if the choice includes "Renewal"? You seem to be using a different measure in that case.

        Can you show the codes of your two measures [Total Sales Amount New Product] and [Total Sales Amount Renew Product]?

        Can you show the structure of your tables as well?