Forum Discussion
Dynamic Pareto segmentation
Hi - Firstly if this post is answered elsewhere please let know :)
Example raw data:
| Warehouse Code | Warehouse Name | Customer Code | Customer Description | Stock Code | Sales $ USD | Quantity |
| 53 | WarehouseA | 884800 | CustomerA | Prod2 | 14.16 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod3 | 19.05228 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod5 | 17.7 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod6 | 7.08 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod7 | 7.08 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod8 | 10.62 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod9 | 17.7 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod12 | 110.97192 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod15 | 32.13612 | 1 |
| 53 | WarehouseA | 884800 | CustomerA | Prod19 | 90.64524 | 1 |
| 53 | WarehouseA | 884801 | CustomerB | Prod4 | 20.12136 | 1 |
| 53 | WarehouseA | 884801 | CustomerB | Prod18 | 91.7922 | 1 |
| 53 | WarehouseA | 884854 | CustomerC | Prod2 | 25.04196 | 1 |
| 53 | WarehouseA | 884960 | CustomerD | Prod6 | 19.63284 | 1 |
| 11 | WarehouseB | 320626 | CustomerE | Prod16 | 40.38432 | 1 |
| 11 | WarehouseB | 365476 | CustomerF | Prod13 | 32.13612 | 1 |
| 12 | WarehouseC | 344305 | CustomerG | Prod14 | 40.38432 | 1 |
| 12 | WarehouseC | 370200 | CustomerH | Prod3 | 28.58196 | 1 |
| 12 | WarehouseC | 383250 | CustomerI | Prod4 | 20.92848 | 1 |
| 12 | WarehouseC | 383250 | CustomerI | Prod10 | 56.09484 | 1 |
| 12 | WarehouseC | 383250 | CustomerI | Prod11 | 56.41344 | 1 |
| 12 | WarehouseC | 383250 | CustomerI | Prod17 | 54.4452 | 1 |
| 14 | WarehouseD | 315336 | CustomerJ | Prod1 | 16.0362 | 1 |
I am trying to summarize my data in a sales by customer/product table like the one below. essentially a summarised 'Pareto' view of my sales data by customer and product.
| Products | |||
| A | B | ||
| Customers | A | 71720 | 16280 |
| B | 16280 | 5830 | |
| Total Sales | 110000 |
I've created a summary 'Customer Code' and 'Product Code' table, and I've managed to create a dynamic set of measures that performs the A/B classification based on the running total, but at the moment I can only generate the calculations if I build them into a report view like the one below:
Current Measure calcs:
1. Sum_Sales = SUMX('YTD Quad Data Source',SUM([Sales $ USD]))
2. Rank = RANKX(ALL('Customer Code'[Cust_Code]),[Sum_Sales])
3. Running_Sales = CALCULATE([Sum_Sales],TOPN([Rank],ALL('Customer Code'[Cust_Code]),[Sum_Sales]))
4. Pareto% = DIVIDE([Running_Sales],CALCULATE([Sum_Sales],'Customer Code'[Cust_Code]))
5. CustAB = IF([Pareto%]<=0.8,"A","B")
My question is - Is there any way to perform these calculations and have the resulting AB ranking defined as a dimension I can group and then change dynamically based on a selected customer or product slicer?
Hopefully I've explained myself well enough for you all to understand the problem :)
Thanks in advance for your assistance
Craig