Outlier Detection Logic at Product/Store/Date level

[_mk7]

EDNA,

Good day. I’m attempting to create outlier detection logic similar to what is outlined within the Detecting and Showcasing outliers lecture, however, I’m having trouble applying the concepts within the lecture to my use-case.

In this situation, I’m trying to group high, low, and non-outliers based at the product/store/date level based on (1) the percentage difference between a forecast for a single product/store/date and the average of sales for the that product/store across multiple dates and (2) the forecast value itself. I’d then like to plot the data points in a scatterplot using the 3 outlier groupings.

I’ve attached sample data and .pbix. The sample data file contains an “Objective” tab which describes provides an example and the expected results.

OutlierEx.pbix (127.8 KB) SampleData.xlsx (13.8 KB)

Any assistance or guidance would be much appreciated. Thanks!

[BrianJ]

@_mk7,

Wow, impeccable timing on this question. I have a YouTube video coming out on Enterprise DNA TV this Friday at 9am ET on almost this exact scenario. Here’s the initial post and solution that prompted me to make the video:

Dynamic grouping into quadrants

@Harvey, This question sparked my interest, so I built out a full sample application based on External Tools Practice Dataset. By adjusting the two sliders associated with the what-if parameters, you can define dynamically what constitutes an “outlier”. Those products that meet the outlier criteria will be colored red in the scatter chart. Below is a publish to web link, where you can play with the application. I’ve also attached the solution file so you can explore how I put this together. Ju…

This is a simpler case than yours, but the video deals with more complex, multidimensional scenarios that mirror your question. Please take a look at the above post, and if that doesn’t provide a sufficient solution for you and you need an answer before Friday am, let me know. Thanks!

• Brian

[_mk7]

Thanks for the quick reply, Brian. The post you provided helps. Looking forward to the upcoming video.

[BrianJ]

@_mk7,

FYI – video I mentioned previously is now live on YouTube.

Write Smarter DAX With Dummy Variables - Power BI

• Brian

[EnterpriseDNA]

Hi @_mk7 , did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @_mk7, we’ve noticed that no response has been received from you since the 18th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[_mk7]

@EnterpriseDNA @BrianJ The video was helpful regarding how to set up multiple dynamic outlier conditions.

Would it be possible to provide some guidance on the DAX required to calculate the percentage difference between a forecast for a single product/store/date and the average of sales for the that product/store across multiple dates (as referenced in the sample data “Objective” tab)?

Thanks!