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!
Solved! Go to Solution.
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."
If I'm understanding your use case correctly...
I think that conceptually would get you what you are looking for.
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