Summing a measure if another measure is within a range of a maximum

[smiths87]

I can't seem to wrap my head around this. I have store level data, and 'Stores Scanning' on the right is a distinct count of the stores scanning for each group by period. You can see in 'Group A' that the store didn't really start the full sell off until Period 2, and then the stores selling was roughly the same for 3 & 4.

What I need to do in the Left table is get a sum of the dollars sold only when that 'Stores Scanning' measure is above a certain threshold for the period. So sum dollars if the number of individual stores scanning for the period is, for example, within 10 stores of the maximum for the 'Stores Scanning' measure across all of the periods for that group. (Which is visualized in the table on the right.)

I've tried several iterations using calculate, summarize, etc, and I just can't get anything to work. I just don't understand how to get the max of the measure for each period, and then use that as a filter.

(Just a little more clarification - for Group A I would want the 'Dollar for only full distro' to be the sum of dollars for only periods 2,3,4, since that is where the distribution was at the fullest. And likewise for each group based on their respective maximum of the measure 'Stores Scanning')

Thanks for looking.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Group	Dollars	Dollars for only full distro			Stores Scanning	Period
2	A	$57,826				Group	9	10	11	12	13	1	2	3	4
3	B	$1,103				A	3	4	5	4	5	5	88	91	90
4	C	$21,503				B	17	47	59	61	55	62	72	71	70
5	D	$278				C	28	29	29	29	30	32	34	36	38
6	Grand Total	$267,627				D	2	23	24	24	24	24	24	24	24
Sheet1

 

[scottsen]

May need you share out a workbook for me to play with; this LOOKS pretty straight forward. Something like:
CALCULATE([TotalSales], FILTER(Sales, [StoresScanning] > [Threshold])

 

[smiths87]

May need you share out a workbook for me to play with; this LOOKS pretty straight forward. Something like:
CALCULATE([TotalSales], FILTER(Sales, [StoresScanning] > [Threshold])

That is what I thought initially. Famous last words on my part. I started with a formula to sum the dollars only if better than a [Threshold], but I could only do it with a hardcoded value. I couldn't conceptualize how to return the [Threshold] as a variable.

Anyway, spreadsheet attached that has a few notes and the basic tables. Thanks.

Google Docs

 

[scottsen]

Not an easy problem. Or at least, not for me. Maybe there is an easier way.... but this is what I think worked. For me.

// A measure that can find the max scanning stores, across time.
MaxStoresScanning:=MAXX(ALL(POS[Period]), [Stores Scanning])

// Helper measure
Scanning Store Delta From Max := ABS([Stores Scanning] - [MaxStoresScanning])

// Go a period at a time, checking if in this period the stores scanning is close to max... and if so, include the period, else... 0.
FilteredPeriodCount :=SUMX(VALUES(POS[Period]), IF([Scanning Store Delta From Max] < 10, [Total Periods], 0))

// Similiar for dollars
FilteredDollars := SUMX(VALUES(POS[Period]), IF([Scanning Store Delta From Max] < 10, [Dollars], 0))

The tricky part for me was using the IF, instead of some flavor of CALCULATE. It does make some sense, because you are interested in your the comparison at an aggregate level, not row by row, which the calculate would do. The outer SUMX is just trickier to make it work for grand totals, not just 1 period at a time.

Best of luck!

 

[FDibbins]

Not really sure I understand what you are trying to do here, but maybe try using =LARGE() within a SUMIF(), where large would be used to calc the threshhold?

 

[smiths87]

Thanks Scottsen! This worked perfectly. Probably take me a while to follow every detail, but I get the broad steps. The If vs. the Calucluate option is very interesting. Can't wait to attempt to wrap my head around that one.

 

[smiths87]

Not really sure I understand what you are trying to do here, but maybe try using =LARGE() within a SUMIF(), where large would be used to calc the threshhold?

I thought about using TOPN in some way, but ScottSen's solution beat me to the punch. Thank god.

 

[FDibbins]

TOPN(), CALCULATE and FILTER are not (as far as I know) native excel functions (maybe they are in VBA, my VBA sucks lol)

 

[smiths87]

TOPN(), CALCULATE and FILTER are not (as far as I know) native excel functions (maybe they are in VBA, my VBA sucks lol)

haha, my vba is mildly rusty as well but I'm working on it. Needed this in a powerpivot solution though. Outside of that would have been pretty easy probably (or at least relatively easy). I like the powerpivot way though, jsut need to study up a little more on it.