Forum Discussion

Reetz's avatar
Reetz
Helper II
5 years ago
Solved

Switch function not working with a slicer

Have been trying to figure this out, watched videos etc, but still can't get this to work. I have a Customer Orders Table for Jan & Feb 2020 and need to annualize Customer Sales and place them in do...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

AlB's avatar
AlB
Community Champion
5 years ago

Hi Reetz 

Link is not working. Must be missing something

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Cheers 

 

 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      None of those links work.

      • Reetz's avatar
        Reetz
        Helper II
        5 years ago

        sorry.  not sure why my links are not working. Hoping these help.  

         

        I have 2 Tables: Customer Sales &  Sales Buckets

        Customer Sales Table
        CustomerSalesDate
        1$101/1/2020
        2$151/1/2020
        3$2001/1/2020
        4$501/1/2020
        1$52/1/2020
        2$602/1/2020
        4$552/1/2020
        5$1,0002/1/2020
        Sales Buckets Table
        MinMaxSales Bucket to DisplayIndex
        0249<2501
        250499250-4992
        500999500-9993
        100049991000-49994
        5000 >=50005

        This is an image of my fields and measures:

        my  4 measures are :

         

        m_annualized Sales =
        var TTLSales=CALCULATE(SUM('Customer Sales'[Sales]))
        return DIVIDE(TTLSales,2)*12
         
        m_annualized Sales bucket =
        if([m_annualized Sales]<250,"<250",
        IF(AND([m_annualized Sales]>=250,[m_annualized Sales]<=499),"250-499",
        IF(AND([m_annualized Sales]>=500,[m_annualized Sales]<=999),"500-999",
        IF(AND([m_annualized Sales]>=1000,[m_annualized Sales]<=4999),"1000-4999",
        ">=5000"))))
         
        m_Sales all measures =
        SWITCH([m_Selected Value],
        "<250",[m_annualized Sales bucket]="<250",
        "250-499",[m_annualized Sales bucket]="250-499",
        "500-999",[m_annualized Sales bucket]="500-999",
        "1000-4999",[m_annualized Sales bucket]="1000-4999",
        ">=5000",[m_annualized Sales bucket]=">=5000")
         
        m_Selected Value = SELECTEDVALUE('Sales Buckets'[Sales Bucket to Display])
         
        My Slicer uses the field Sales Bucket to Display. 
         
        Reattaching my power bi screen print.  In the example below, by selecting slicer option 500-999, my Sales Bucket Table should only show customer # 4.  I'm just not sure what i'm doing wrong.