I need to calculate the cumulative % of sales by customer to identify which customers make up our top X% of sales. I also need to do the same for products but would create a separate expression for that dimension. That said, users want to be able to apply various filters related to customers or products and have the % of sales re-calculated in real-time.
Example granularity of Sales data below ...
| Date | Customer | Region | Territory | State | Product | Product Line | Product Type | Sales Revenue |
| 201801 | A | 1 | A | A | 123 | 1 | A | $100 |
| 201801 | A | 1 | A | A | 234 | 2 | B | $200 |
| 201801 | B | 2 | A | B | 123 | 1 | A | $200 |
| 201801 | B | 2 | A | B | 123 | 1 | A | $300 |
| 201801 | C | 2 | B | C | 345 | 2 | C | $700 |
The expression must calculate aggregate sales for the dimension, ideally sort the dimension in descending order by sum of sales in case the user has the table sorted by something else, and show the cumulative sales and cumulative % of sales based on the currently selected data (zero-to-many) filters applied. Please see below for additional clarity ... is this possible?
Ex. Sales by Customer - No Filters
| Customer | Sales | Cumulative Sales | Cumulative % of Sales |
| C | $700 | $700 | 47% |
| B | $500 | $1,200 | 80% |
| A | $300 | $1,500 | 100% |
Ex. Sales by Customer - Filtered to Region 2
| Customer | Sales | Cumulative Sales | Cumulative % of Sales |
| C | $700 | $700 | 58% |
| B | $500 | $1,200 | 100% |
Ex. Sales by Product - No Filters
| Product | Sales | Cumulative Sales | Cumulative % of Sales |
| 345 | $700 | $700 | 47% |
| 123 | $600 | $1,300 | 87% |
| 234 | $200 | $1,500 | 100% |
Ex. Sales by Product - Filtered to Region 2
| Product | Sales | Cumulative Sales | Cumulative % of Sales |
| 345 | $700 | $700 | 54% |
| 123 | $600 | $1,300 | 100% |
Please help ... I cannot figure this out.
