Filter or GroupBy to Keep StDEV Formula Independent for the 5 Companies

[james_pease]

Hello all! I have successfully created a Outlier Logic formula:

Outlier? = IF([Cost %]>0,

            IF(OR('Measures Table'[Lower Cost % STDEV]>= [Cost %],

            [Cost %] >='Measures Table'[Upper Cost % STDEV]), "Yes", "No"))

However, as soon as I drill down to a specific store, the STDEV formulas are recalculated for that single store (row) and go figure it will result in "No" so I know my error is with the Upper and Lower STDEV formulas.

I need help to ensure the Upper and Lower STDEV is evaluated for the specific company a store is under (So all store in a company, but not all stores in the corporation). We have 5 companies so thats where I am having issues with. Please see below for context.

Lower Cost % STDEV = Calculate([cost %] - [Outlier STDEV],

                        ALLSELECTED())

Upper Cost % STDEV = Calculate([cost %] + [Outlier STDEV],

                      ALLSELECTED())

Outlier STDEV = CALCULATE(1.5* [Cost % STDEV])   

(I know statistically 2.5*STDEV is considered an outlier but my company wants the window tighter)

Cost % STDEV = Calculate(STDEVX.P('All_Actual_Expenses', [Cost %]),ALLSELECTED())

Cost % = IF(sum(All_Actual_Sales[Actual Sales])>10000,

            sum(All_Actual_Expenses[Amount])/sum(All_Actual_Sales[Actual Sales]))

(I set this formula to require sales be greater than 10,000 to ensure the store was open for at least 1 day, aka remodels and closed stores are ignored)

So again to clarify, I need help with changing the Upper and Lower STDEV formulas to be calculated for the 5 separate company based on what I filter to on the data slicer. So if I pick 1 store or all stores, I want them to be evaluated based on the company's Upper and Lower STDEV that they reside in. In the Store List table, one of the columns is company. I was trying to do a groupby company name but thats probably wrong haha.

Thank you in advance!! Please let me know if you need more context.

[james_pease]

So I could not find a way to keep the measures independent for the companies. So I am going to create 5 separate scatter plots and use bookmarks so when a bookmark is selected, the scatterplot  and measures table will populate all the stores under that company. Wanted to share in case anyone in the future comes across a similar situation. 

[daXtreme]

Hi @james_pease 

2 things to address here. First, your formulas (at least some of them) are inefficient as they repeat code instead of capturing values into variables. Second, it's perfectly doable what you want but I have to see the model. How can one give you a good answer without seeing the model/data? I don't want to guess, so please supply the model and I'll then give you the formula you want. Cheers.