Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)"
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
Like the attached?
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.
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
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.