Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
richchester
Regular Visitor

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

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 NameItem NumberDiscountGroup Name
EastA1611075BrandX
EastA2011066BrandX
EastA311060BrandX
EastA411075BrandX
EastA511075BrandY
EastA611075BrandY
EastA911075BrandX
EastA9211092BrandX
WestA1011079.75BrandX
WestA10111077.5BrandY
WestA10311077.5BrandY
WestA10511079.75BrandX
WestA10911077.5BrandY
WestA1111079.75BrandX
WestA11511077.5BrandY
WestA13111077.5BrandY
WestA3211079.75BrandX
WestA5911089BrandX
WestA6011095BrandX
WestA711072BrandY
WestA811070BrandY
WestA9011077.5BrandY
WestA9111077.5BrandY

 

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

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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

jdbuchanan71_0-1636467630836.png

 

View solution in original post

2 REPLIES 2
richchester
Regular Visitor

@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
Super User
Super User

@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

jdbuchanan71_0-1636467630836.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors