Switch function not working with a slicer

[Reetz]

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 dollar buckets.  Then using a slicer with those buckets, display just those customers and their annualized sales that fall into the sales bucket selected.  In the example below, selecting 500-999 should only display customer 4 in the Sales Buckets Table.  Thanks in advance!!!!!

See attached Power BI.  https://www.dropbox.com/home/Power%20BI?preview=Sales+Buckets.pbix

[AlB]

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 

[Reetz]

https://www.dropbox.com/home/Power%20BI?preview=Sales+Buckets.pbix 

Here is the link again.  Clicking on it says that you must download it first. 

Or possibly via this link using my shared drive.  https://stryker-my.sharepoint.com/:u:/r/personal/rita_stump_stryker_com/Documents/Power%20BI/Power%2...

[Ashish_Mathur]

Hi,

None of those links work.

[Reetz]

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

I have 2 Tables: Customer Sales &  Sales Buckets

Customer Sales Table Customer Sales Date 1 $10 1/1/2020 2 $15 1/1/2020 3 $200 1/1/2020 4 $50 1/1/2020 1 $5 2/1/2020 2 $60 2/1/2020 4 $55 2/1/2020 5 $1,000 2/1/2020

Sales Buckets Table Min Max Sales Bucket to Display Index 0 249 <250 1 250 499 250-499 2 500 999 500-999 3 1000 4999 1000-4999 4 5000   >=5000 5

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Reetz]

This is Great!!!!  Thank you!  Just curious, I thought I had to use the Switch function to make this work.  Is this just a different approach?

Thanks again!!

[Ashish_Mathur]

Thank you.  Yes, a completely different appraoch based on measures only and of course a disconnected table.