Percentage Difference at Product/Store/Date level

DAX DAX Calculations
1

EDNA,

I appreciate your helpful video for managing dynamic outliers using dummy variables (https://www.youtube.com/watch?v=Q401JNywTKY).

In my 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.

Would it be possible to provide 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,
MK

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

2

@_mk7,

See if this gets you what you need:

image
image1010×492 36.6 KB

Key measure is:

Average Sales by Store and Product = 

CALCULATE(
    [Average Sales],
    ALLEXCEPT( DimProduct, DimProduct[Product] ),
    ALLEXCEPT( DimStore, DimStore[Store] ),
    REMOVEFILTERS( FactSalesFcst[Date], FactSalesFcst[Forecast] )
)

Other measures branch pretty straightforwardly off of this one.

I hope this is helpful. Full solution file attached.

  • Brian

eDNA Formula - OutlierEx Solution.pbix (131.0 KB)

1 Like