Removing sales growth outliers from total sales, sales YTD and sales PYTD

[NEssmyer]

I've been reviewing a ton of suggestions for identifiying outliers within my dataset, but nothing that is quite close to what I need to accomplish.

I've got a list of stores and their sales revenue for the previous 18 months. Not all stores had sales during each month or each year for that matter. Some stores saw exponetial growth while others have dramatically decreased sales.

My range for what I define as an outlier may change over time, but currently it's any store that is outside of the -200% or 200% sales growth YoY.

To define my Sales Growth % I'm using a couple of measures.

1. Total Sales - 

CALCULATE(sum('Sales Data'[ExchangeRate]), 'LFL Units'[Sales LFL Test]= 1, FoodKeyTablePC[CloseDateCheck]=1)

// the two filers included are to isolate stores with sales in both years and to confirm that the store is still open

2. Total Sales YTD - 

CALCULATE(TOTALYTD([Sales Total], 'Date Table'[Date], ALL('Date Table'), "08/31"))

// my fiscal year begins 9/1

3. Total Sales PYTD - 

CALCULATE(TOTALYTD([Sales Total], 'Date Table'[Date], ALL('Date Table'), "08/31"), SAMEPERIODLASTYEAR('Date Table'[Date]))

4. Sales Growth % - 

DIVIDE(([Sales YTD]-[Sales PYTD]),

[Sales PYTD],

BLANK()

)

While I can manually apply a visual level filter to some visuals that exclude sales growth outside of the identified range, I can't apply it to all. Additionally if that range changes at some time it would force me to update every visual where it's included vs a measure that I could update once. I've tried filtering total sales by the sales growth, which creates a circular reference. I've tried to eliminate the growth % where its outside of the range, but it doesnt impact the Sales YTD or Sales PYTD. Essentially is a store is an outlier, I don't want to see it anywhere.

Any help would be greatly appreciated.

[Greg_Deckler · Accepted solution]

Very difficult to give you a specific answer with the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

However, if you want to exclude some stores in certain calculations, you should be able to structure your calculations like this:

Measure =
  VAR __Table =
    FILTER(
      SUMMARIZE(
        'Sales Table',
        [Store ID],
        "__SalesGrowth%",[Sales Growth %]
      ),
      [__SalesGrwoth%] > -2 && [__SalesGrowth%] < 2
    )
RETURN
  <some calculation over __Table>

[littlemojopuppy]

I just want to add that @Greg_Deckler's solution is more elegant than mine 😉

[littlemojopuppy]

This isn't exactly an elegant solution, but you could a calculated column in your store table (assuming they are separate tables)

% Change =
VAR
	CYTD = --insert your formula for YTD here
VAR
	PYTD = --insert your formula for PYTD here
RETURN

DIVIDE
	CYTD,
	PYTD,
	BLANK()
)

That would make it very easy to calculate % change per store.

From there excluding stores becomes very simple...
FILTER(

     Stores,
     [%Change] <= 2 &&

     [%Change] >= -2

)

[littlemojopuppy]

Going to correct myself on something...your formulas should be like this:

CYTD = SUMX(
	FILTER(
		RELATEDTABLE(Sales),
		--Set Filter for this year
	Sales
)

PYTD = CALCULATE(
	CYTD,
	SAMEPERIODLASTYEAR(Dates[Date])
)

[amitchandak]

for this year and last year you can try like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

for outliers

https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-box-and-whisker-plots/

https://www.mssqltips.com/sqlservertip/5547/anomaly-detection-without-any-coding-using-power-bi/

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381449?src=office&tab=Overview