Eliminating insignificant data

[hobanwashburne]

Given the following sample data set  

Branch	Product Line	FY 13	FY 14	FY 15	Average	Percent
A	100	13,150	19,768	15,924	16,281	15%
A	200	11,762	16,894	13,253	13,970	13%
A	300	175	185	157	172	0%
A	400	17,383	13,277	11,575	14,078	13%
A	500	19,807	12,934	16,306	16,349	15%
A	600	14,057	18,529	18,072	16,886	15%
A	700	18,988	16,344	18,676	18,003	16%
A	800	19	14	12	15	0%
A	900	18,879	11,578	10,084	13,514	12%
Subtotal					109,267	100%
B	100	1,954	1,385	1,055	1,465	14%
B	200	1,596	1,404	1,749	1,583	15%
B	300	1,093	1,920	1,139	1,384	13%
B	400	2,000	1,098	1,128	1,409	13%
B	500	1,304	1,487	1,614	1,468	14%
B	600	1,883	1,497	1,972	1,784	17%
B	700	15	13	12	13	0%
B	800	1,703	1,040	1,635	1,459	14%
Subtotal					10,565	100%

For Branch A you can see that Product Lines 300 and 800 make up very little of their overall sales. Likewise for Product Line 700 in Branch B. Is there a way to identify these in the load script and remove them from the data set. Something similar to the Dimension Limits tab "Show only values that accumulate to 80% of the Total (for each branch)"

[sunny_talwar]

Like the attached?

[hobanwashburne]

Not quite,

The final total must recalculate back to 200% (100% per branch) without the identified product lines < as if they were never there>. Also it would be preferable to use accumulates to x% per branch rather than setting a lower bound.

[sunny_talwar · Accepted solution]

May be like this. Personally, I don't like this because we are doing so many aggregation for something which seems very insignificant and something we can handle on the front end. But check it out

[hobanwashburne]

Remember, this is a sample data set. The actual data set is comprised of 2 countries, 28 branches, 3 distribution channels, and 675 product lines. I'm also trying to use a combination of Order By and looping to get to the 80% per branch.