Dynamic Pareto segmentation

SMITTY_8000408 — Tue, 25 Jul 2017 02:24:31 GMT

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

topic Dynamic Pareto segmentation in DAX Commands and Tips

Dynamic Pareto segmentation