Using IF Statement to Group Rounding Issues

[bwortham32]

Hello All,

I have some raw data where I'm rounding the price to the nearest hundreth - rounding to the nearest tenth is not accurate enough.  I have similar prices that should be grouped to the same price level.  For example, in my data below, $13.60 and $13.63 should be grouped to the $13.62 price level with the corresponding new totals:

Before:

BRAND	PRICE	QTY	% of Business	Sales
OGIO	13.6	5	18%	$        68.00
OGIO	13.62	20	71%	$     272.40
OGIO	13.63	3	11%	$        40.89

After:

BRAND	PRICE	QTY	% of Business	Sales
OGIO	13.62	28	100%	$     381.36

I would know the correct price level to use by taking the level with the highest % of business.  Any ideas on how to group these other price levels into the correct one would be very appreciated.

Thanks!

[bwortham32]

Maybe I should clarify.. In the end, I want to say "IF the price level is less than 20% of business AND +/- $0.15 cents from the correct price level of $13.62, group it with $13.62." 

[RodL]

If I'm understanding your use case correctly...

• you would identify the "standard" price with a Summarize tool (grouped by the Brand I assume?) and find the Max % of Business
• follow with a Formula tool to calculate the boundaries of the +/- 15 cents
• join back to original data flow on Brand and from the unmatched data anchor, attach a Filter that filters on the <20% value and whether the price falls within the +/- range
• From the True side of the Filter, join back to the Summarize to and assign the price from that.

I think that conceptually would get you what you are looking for.

[Joe_Mako · Accepted solution]

Building from Rod's description, how about the attached?

Sort - ensure records are in the order needed
Multi-Row Formula - identify records that are first, meaning greatest "% of Business" value
Sample - to select just the first record
Join - to bring the price info from first record to all all other records in BRAND
Filter - split stream into record that can be aggregated together (that meet the condition), and those that do not
Formula - recalculate the Sales value
Summarize - aggregate the data
Union - bring all records together