Filtering Table to Items whose discount is > 1 standard deviation from the mean

[richchester]

Good day!  I have a need to create a report that identifies items whose discount within their price book exceeds the value 1 SD above the average for that price book.  I have mulitple price books, and multiple item groups.  Users select book(s) and group(s), the Average and SD must calculate for each selected pricebook -  for only the items in the selected group(s) -- and then show the items whose discount is higher than the calculated value.

Please consider this data:

Price Book Name	Item Number	Discount	Group Name
East	A16110	75	BrandX
East	A20110	66	BrandX
East	A3110	60	BrandX
East	A4110	75	BrandX
East	A5110	75	BrandY
East	A6110	75	BrandY
East	A9110	75	BrandX
East	A92110	92	BrandX
West	A10110	79.75	BrandX
West	A101110	77.5	BrandY
West	A103110	77.5	BrandY
West	A105110	79.75	BrandX
West	A109110	77.5	BrandY
West	A11110	79.75	BrandX
West	A115110	77.5	BrandY
West	A131110	77.5	BrandY
West	A32110	79.75	BrandX
West	A59110	89	BrandX
West	A60110	95	BrandX
West	A7110	72	BrandY
West	A8110	70	BrandY
West	A90110	77.5	BrandY
West	A91110	77.5	BrandY

If My user picks Brand X in a slicer, then...

For the East Price Book, the Average is 73.83, the Std Dev is 9.89, and 1SD above the average is 83.72.  In my output, I'd have 1 row for the East price book -- for item A92110.

For the West price book, the average is 83.83, the Std Dev is 6.03 and 1SD above the average is 89.96.  In my output I'd again have just 1 row of output for this price book -- item A60110.

I've tried for many, many hours to come up with the DAX that would enable me to create this report -- and I'm pretty sure my issue is context... but I can't seem to get this to work.

I'd appreciate any thoughts you have on how I'd go about solving this particular requirement.  Thank you in advance for your insights!

Rich

[richchester]

@jdbuchanan71 thank you very much for your solution.

Simple and elegant.  My problem appears (once again) to be my tendency to overcomplicate things!  I will explore your solution with my full data set - but based on what's shown in the sample set, you've got it nailed.

Have a great day - and thanks again!

Rich

[jdbuchanan71 · Accepted solution]

@richchester 

I think I was able to get this to work.  Take a look at the attached .pbix file.  There is a filter applied on the lower visual for the [Check Measure] is 1